匿名用户
1级
2015-11-18 回答
SELECT lzh,lch, count(*) AS zs FROM kfxx ----这里Select中要有分组的数据才可以吧?!
WHERE kfzt='01'
GROUP BY lzh,lch
你题目中说的,count为0的数据,是不是指kfzt='01'的? 但kfzt为其它值的,还是有数的?
追问:
恩,其中一个分组里没有kfzt=‘01’的,想显示0,但是我执行代码的时候直接略过了,从下面一个开始了
追答:
用union来实现
SELECT lzh,lch, count(*) AS zs FROM kfxx
WHERE kfzt='01'
GROUP BY lzh,lch
union
select distinct lzh, lch, 0 from kfxx
WHERE kfzt!='01' ;
或
select lzh,lch , case
when kfzt='01' then count(*) else 0 end as zs
FROM kfxx
GROUP BY lzh,lch
追问:
还不对。。比如
lzh lch kfzt
1 1 01
1 1 01
1 2 02
1 3 01
我想得出kfzt为01的数量
lzh lch zs
1 1 2
1 2 0
1 3 1
追答:
select lzh,lch , case
when kfzt='01' then count(*) else 0 end as zs
FROM kfxx
GROUP BY lzh,lch;
mysql> select * from kfxx ;
+------+------+------+
| lzh | lch | kfzt |
+------+------+------+
| 1 | 1 | 01 |
| 1 | 1 | 01 |
| 1 | 2 | 02 |
| 1 | 3 | 01 |
+------+------+------+
4 rows in set (0.00 sec)
mysql> select lzh,lch , case
-> when kfzt='01' then count(*) else 0 end as zs
-> FROM kfxx
-> GROUP BY lzh,lch ;
+------+------+------+
| lzh | lch | zs |
+------+------+------+
| 1 | 1 | 2 |
| 1 | 2 | 0 |
| 1 | 3 | 1 |
+------+------+------+
3 rows in set (0.00 sec)
追问:
1 2 02下新增一行
1 2 01
如果这样,好像还会是
1 2 0
期望是
lzh lch zs
1 1 2
1 2 1
1 3 1
快要对了,我基础比较差,帮帮我啊
追答:
mysql> select lzh, lch, sum(zs) from (
-> select lzh,lch , case
-> when kfzt='01' then 1 else 0 end as zs
-> from kfxx ) as tmp_kfxx
-> group by lzh, lch ;
+------+------+---------+
| lzh | lch | sum(zs) |
+------+------+---------+
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
+------+------+---------+
3 rows in set (0.02 sec)