即使条件不适用，也获取计数

Question

我有两个表

1）文档：代表文档

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

2）目的地：代表文档的一方

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

状态列代表用户的文件夹，我想为每种文件夹的每种类型的计数，即使该文件夹为specifi用户为空，但是，如果需要他们，

+--------+--------+--------------+--------------+--------------+
| 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       |
+--------+--------+--------------+--------------+--------------+

我面临的问题是，我找不到一种方法来获得用户没有加入的类型，我可以使用案例，但不能以我想要的形式

我的查询是：

`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