SQL心得：在含有GROUP BY的SELECT语句中显示COUNT()为0的结果

1.问题

如下ExampleTable表，求各种类（CategoryID）满足Flag等于1的记录数。

ID   FlagCategoryID

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语句完整的执行顺序

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 GROUP BY CategoryID) AS SubTable

ON MainTable.CategoryID = SubTable.CategoryID

执行后即可返回正确的结果：

CategoryID     TotalNum

1     2

2     2

3     0

　　　　　　　　　　表4-正确输出结果