sql-server - 即使条件不适用也要计数

Question

我有两张 table

1) Document:代表一个文档

+----+----------+------+
| ID | Body     | Type |
+----+----------+------+
|  1 | Ramesh   |  1   |
|  2 | Khilan   |  1   |
|  3 | kaushik  |  4   |
|  4 | Chaitali |  2   |
|  5 | Hardik   |  2   |
+----+----------+------+

2)Destination:代表文档的一方

+--------+------------+--------+
| UserId | DocumentId | Status |
+--------+------------+--------+
|   6    |      3     |    4   |
|   4    |      5     |    5   |
|   89   |      2     |    0   |
|   15   |      4     |    3   |
|   89   |      1     |    0   |
+--------+------------+--------+

状态列代表用户的文件夹，我想获取每个文件夹的每种类型的计数，即使文件夹对于特定用户来说是空的，然而，如果想要他们从这里开始，

+--------+--------+--------------+--------------+--------------+
| UserId | Status | Type 1 Count | Type 2 Count | Type 4 Count |
+--------+--------+--------------+--------------+--------------+
|   89   |    0   |      2       |      0       |      0       |
|   89   |    3   |      0       |      0       |      0       |
|   89   |    4   |      0       |      0       |      0       |
|   89   |    5   |      0       |      0       |      0       |
+--------+--------+--------------+--------------+--------------+

我面临的问题是我找不到通过加入获取用户没有的类型的方法，我可以使用 CASE 获取它们，但不是我想要的形式

我的查询是:

`SELECT dd.[Status],  
    SUM(CASE WHEN d.[Type] = 1 THEN 1 ELSE 0 END) AS 'Type1Count'  
    SUM(CASE WHEN d.[Type] = 2 THEN 1 ELSE 0 END) AS 'Type2Count'  
    SUM(CASE WHEN d.[Type] = 4 THEN 1 ELSE 0 END) AS 'Type4Count'  
 FROM [User] u LEFT JOIN [Destination] dd ON u.[Id] = dd.[UserId]  
    LEFT JOIN [Document] d ON dd.[DocumentId] = d.[Id]  
 WHERE u.[Id] = @UserId`

结果是

+--------+--------+--------------+--------------+--------------+
| UserId | Status | Type 1 Count | Type 2 Count | Type 4 Count |
+--------+--------+--------------+--------------+--------------+
|   89   |    0   |      2       |      0       |      0       |
+--------+--------+--------------+--------------+--------------+

Answer 1

因此，在加入 Document 和 Destination 之前，将所有用户加入到一个包含所有状态的表格中(我已根据您在问题中的描述命名了此文件夹):

SELECT u.UserId, st.Status, 
SUM(CASE WHEN doc.Type = 1 THEN 1 ELSE 0 END) AS [Type 1 Count],
SUM(CASE WHEN doc.Type = 2 THEN 1 ELSE 0 END) AS [Type 2 Count], 
SUM(CASE WHEN doc.Type = 4 THEN 1 ELSE 0 END) AS [Type 4 Count]

FROM User u

CROSS JOIN Folder st

LEFT OUTER JOIN Destination d
ON d.UserId = u.UserId
AND d.Status = st.Status

LEFT OUTER JOIN Document doc
ON doc.ID = d.DocumentId

GROUP BY u.UserId, st.Status

ORDER BY u.UserId