Question

mysql count 返回值为0的结果

level count
1 13
2 2
3 0
4 1
5 2
大家看看这个我用了count列出了对应level中分别有多少条记录，但是level3中没有记录，怎么让mysql返回这样的结果呢？外连接也试过了，我是菜鸟，大家帮帮忙吧。。。

Answer 1

是在想不出你的表结构和你的实际情况，我举个例子供你参考，你根据你自己的实际情况调整下吧。

mysql> select * from t1;
+-------+------+
| level | name |
+-------+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | a |
| 2 | b |
| 4 | a |
| 5 | b |
+-------+------+
7 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.02 sec)

mysql> select t2.id as level,count(case when name is not null then 1 end) as count
-> from t2 left join t1 on t2.id = t1.level
-> group by t2.id;
+-------+-------+
| level | count |
+-------+-------+
| 1 | 3 |
| 2 | 2 |
| 3 | 0 |
| 4 | 1 |
| 5 | 1 |
+-------+-------+
5 rows in set (0.00 sec)

Answer 2

是在想不出你的表结构和你的实际情况，我举个例子供你参考，你根据你自己的实际情况调整下吧。
mysql>
select
*
from
t1;
+-------+------+
|
level
|
name
|
+-------+------+
|
1
|
a
|
|
1
|
b
|
|
1
|
c
|
|
2
|
a
|
|
2
|
b
|
|
4
|
a
|
|
5
|
b
|
+-------+------+
7
rows
in
set
(0.00
sec)
mysql>
select
*
from
t2;
+------+
|
id
|
+------+
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
+------+
5
rows
in
set
(0.02
sec)
mysql>
select
t2.id
as
level,count(case
when
name
is
not
null
then
1
end)
as
count
->
from
t2
left
join
t1
on
t2.id
=
t1.level
->
group
by
t2.id;
+-------+-------+
|
level
|
count
|
+-------+-------+
|
1
|
3
|
|
2
|
2
|
|
3
|
0
|
|
4
|
1
|
|
5
|
1
|
+-------+-------+
5
rows
in
set
(0.00
sec)

Answer 3

select level,count(*) from 表
group by level

Answer 4

select level,count(*) as count from tab_name group by level

Answer 5

你自己能看懂自己的问题么？