做报表的时候经常会用到聚合函数,有时候可能需要在一个查询里面写多个Count,只是Count的条件不一样。这种要怎么写呢?
我们可以用子查询来做这样的事:
SELECT
Count_1=(SELECT Count(*) FROM Table1 WHERE Reference = 1),
Count_2=(SELECT Count(*) FROM Table1 WHERE Reference = 2)
如果需要分组统计,上面语句就有心无力了,于是我们这样写:
示例数据:
Departments PK - DeptID DeptName
--------------------------
1 Department 1
2 Department 2
3 Department 3
4 Department 4
Groups PK - GroupdID DeptID
--------------------------
1 1
2 1
3 3
4 4
5 2
6 3
7 1
8 3
Inventory PK - ItemID GroupID
--------------------------
1 2
2 3
3 8
4 1
5 4
6 5
7 1
8 2
9 2
10 3
11 7
示例代码:
SELECT d.deptID,
COUNT(DISTINCT g.GroupID) AS Groups,
COUNT(DISTINCT i.ItemID) AS Items
FROM Departments d
LEFT JOIN Groups g ON g.deptID = d.deptID
LEFT JOIN Items i ON i.GroupID = g.GroupID
GROUP BY d.deptID
得到结果:
deptID Groups Items
----- ------ -----
1 3 6
2 1 1
3 3 3
4 1 1
也许你希望在Groups或Items里面没有记录则返回0,此时可以使用Left Outer Join。
在Left Outer Join里面写子查询也是可以的,我也经常这样用。实际上上面的查询如果还要在Count的同时进行过滤则需要用到子查询了。
SELECT d.deptID,
g.COUNT1 AS Groups
FROM Departments d
LEFT OUTER JOIN (SELECT deptID, COUNT(DISTINCT g.GroupID) AS COUNT1 FROM Groups WHERE GroupID > 2
GROUP BY DeptID
) as g on g.DeptID = d.DeptID