十九画生

永不放弃

 

【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

恩,学习还是要多交流,大家的思想碰撞总能得出更好的方案。

posted on 2011-08-09 23:49  十九画生  阅读(11626)  评论(17编辑  收藏

导航

统计