【SQL】在含有GROUP BY的SELECT语句中如何显示COUNT()为0的结果
1.问题
如下ExampleTable表,求各种类(CategoryID)满足Flag等于1的记录数。
ID | Flag | CategoryID |
1 | 1 | 1 |
2 | 1 | 1 |
3 | 1 | 2 |
4 | 1 | 2 |
5 | 0 | 3 |
表1-ExampleTable
理想的结果应该如下:
CategoryID | TotalNum |
1 | 2 |
2 | 2 |
3 | 0 |
表2-理想输出结果
初看此问题,我们很容易写出如下语句:
SELECT CategoryID,COUNT(1) AS TotalNum FROM ExampleTable WHERE Flag = 1 GROUP BY CategoryID
可运行之后得到的结果如下:
CategoryID | TotalNum |
1 | 2 |
2 | 2 |
表3-错误输出结果
没有CategoryID=3,TotalNum=0的记录。
2.原因分析:
造成以上结果的原因是因为在SELECT语句中WHERE子句先于GROUP BY执行,因此在执行GROUP BY子句时
表中的记录已经将Category = 3的记录过滤,分组处理中自然不会计算在内。
补充:SQL SELECT语句完整的执行顺序
SQL SELECT语句完整的执行顺序:
1、FROM子句组装来自不同数据源的数据;
2、WHERE子句基于指定的条件对记录进行筛选;
3、GROUP BY子句将数据划分为多个分组;
4、使用聚集函数进行计算;
5、使用HAVING子句筛选分组;
6、计算所有表达式;
7、使用ORDER BY对结果进行排序。
3.解决方案:
构造含有所有CategoeyID的结果集与其上语句所得结果集进行连接,并利用NULL替换函数
(如SQL SERVER中的ISNULL()、ORACLE中的NVL())将NULL替换为0。
示例语句如下:
SELECT DISTINCT MainTable.CategoryID,ISNULL(SubTable.SubNum, 0) AS TotalNum
FROM ExampleTable AS MainTable
LEFT JOIN
(
SELECT CategoryID,COUNT(1) AS SubNum FROM ExampleTable WHERE Flag = 1 GROUPBY CategoryID
) AS SubTable
ON MainTable.CategoryID = SubTable.CategoryID
执行后即可返回正确的结果:
CategoryID | TotalNum |
1 | 2 |
2 | 2 |
3 | 0 |
表4-正确输出结果
看了大家的回复,得出一个更好更通用的方法,感谢大家:
SUM法:
如果Flag值只有0和1的话,直接一条语句
SELECT CategoryID,SUM(flag) AS TotalNum FROM tbl_category_test GROUP BY CategoryID
如果Flag还有其他值的话,可以利用CASE WHEN构造子查询:
SELECT CategoryID,SUM(IsRight) AS TotalNum FROM ( SELECT CategoryID,CASE WHEN Flag = 1 THEN 1 ELSE 0 END AS IsRight FROM ExampleTable ) AS tempTable GROUP BY CategoryID
恩,学习还是要多交流,大家的思想碰撞总能得出更好的方案。