我正在尝试创建一个报告来查找订阅通知类型的用户数。
我被子查询困住了，因为如果这两个表
表1
通知大师
+----+-------+
| ID | Name |
+----+-------+
| 1 | Email |
| 2 | Push |
| 3 | Call |
+----+-------+
表2
通知首选项
+------------+------------------+------------+--------------+
| ResourceID | NotificationID | IsChecked | AccountID |
+------------+------------------+------------+--------------+
| 23 | 1 | 1 1 |
| 36 | 2 | 0 2 |
| 45 | 3 | 1 3 |
| 23 | 1 | 0 1 |
| 36 | 2 | 1 2 |
| 45 | 3 | 0 3 |
| 23 | 1 | 1 1 |
| 36 | 2 | 0 3 |
| 45 | 3 | 1 3 |
+------------+------------------+--------------------------+
预期输出
通知与资源计数
+----------+-------+------+------+
| Accountid Email | Push | Call |
+----------+-------+------+------+
| 1 | 2 | 1 | 2 |
+----------+-------+------+------+
其他表格
帐户名称
+----+-------+
| ID | Name |
+----+-------+
| 1 | Blues |
+----+-------+
| 2 | Jazz |
+----+-------+
| 3 | Rock |
+----+-------+
资源名称
+----------+----------------+-----------+
| Resource | Name | AccountID |
+----------+----------------+-----------+
| 23 | MJ | 1 |
| 36 | Paul | 1 |
| 45 | Jay Z | 3 |
+----------+----------------+-----------+
进展至今
SELECT A.ID
,A.Name
,count(R.id) AS 'Total Resource Count'
,(SELECT count(DISTINCT np.resourceid)
FROM NotificationPreference np
INNER JOIN NotificationMaster nm ON np.notificationid = nm.id
WHERE np.accountid = A.ID
AND nm.id = 1
) AS 'Email'
FROM AccountName A
LEFT JOIN [ResourceNames] R ON A.ID = R.[AccountID]
LEFT JOIN NotificationPreference np ON np.resourceid = R.ID
GROUP BY A.ID
,A.Name
基本枢轴使用条件COUNT()：
SELECT Accountid
, COUNT( CASE WHEN nm.Name = 'Email' THEN 1 END ) as Email
, COUNT( CASE WHEN nm.Name = 'Push' THEN 1 END ) as Push
, COUNT( CASE WHEN nm.Name = 'Call' THEN 1 END ) as Call
FROM NotificationPreference np
JOIN NotificationMaster nm
ON np.NotificationID = nm.id
GROUP BY Accountid