mysql计数行和行与null

Question

hy伙计们，我有这样的桌子：

+----+------+
| id | grade| 
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    2 |
|  4 | NULL |
|  5 | NULL |
|  6 | NULL |
+----+------+

其中

1坏

2好

3很好

我试图获得类似的结果：

+--------------+------+
| grade        | count| 
+--------------+------+
| "Bad"        |    2 |
| "Good"       |    1 |
| "Very Good"  |    0 |
| "Not Ranked" |    3 |
+--------------+------+

im tryng and count，但没有成功

Answer 1

您可以使用CASE更改grade IBNO的值，

SELECT  CASE WHEN grade = 1 THEN 'Bad'
            WHEN grade = 2 THEN 'Good'
            WHEN grade = 3 THEN 'Very Good'
            ELSE 'Not Ranked'
        END as grade,
        COUNT(IFNULL(grade, 0)) as `count`
FROM    TableName
GROUP   BY CASE WHEN grade = 1 THEN 'Bad'
            WHEN grade = 2 THEN 'Good'
            WHEN grade = 3 THEN 'Very Good'
            ELSE 'Not Ranked'
        END

由于您想显示所有值，因此需要创建一个返回所有值并使用LEFT JOIN加入表的子查询。

SELECT  a.grade,
        COUNT(b.id)
FROM    
        (
            SELECT 1 id, 'Bad' grade UNION ALL
            SELECT 2 id, 'Good' grade UNION ALL
            SELECT 3 id, 'Very Good' grade UNION ALL
            SELECT 999 id, 'Not Ranked' grade 
        ) a
        LEFT JOIN TableName b ON a.id = IFNULL(b.grade, 999)
GROUP   BY a.grade

这是一个演示。

Answer 2

如果您可以使用数字，并以后将它们与字符串匹配，则可以是一个解决方案：

SELECT grade, count(*) as id FROM tbl GROUP BY grade

源