sql - 如何使用另一个表中的值计数

Question

如何使用不同表的计数连接。请看看我的查询。我在这里使用 CROSS APPLY。但我没有得到实际结果。我如何从不在事件表中的项目表中获取所有项目。

标签:Inc_cat

+------------+--------------+--+
| inc_cat_id |  inc_cat_n   |  |
+------------+--------------+--+
|          1 | Support      |  |
|          2 | PM           |  |
|          3 | Installation |  |
+------------+--------------+--+

表:事件

+-------------+---------+------------+-----------------+
| incident_id | item_id | inc_cat_id | date_logged     |
+-------------+---------+------------+-----------------+
|         100 |     555 |          1 |  2016-01-01     |
|         101 |     555 |          2 |  2016-01-18     |
|         103 |     444 |          3 |  2016-02-10     |
|         104 |     444 |          2 |  2016-04-01     |
|         105 |     666 |          1 |  2016-04-09     |
|         106 |     555 |          2 |  2016-04-20     |
+-------------+---------+------------+-----------------+

表格:项目

+---------+---------+--+
| item_id | cust_id |  |
+---------+---------+--+
|     444 |      34 |  |
|     555 |      34 |  |
|     666 |      76 |  |
|     333 |      34 |  |
|     222 |      34 |  |
|     111 |      34 |  |
+---------+---------+--+

结果:

+---------+----------------+-----------+---------------------+
| item_id | count(Support) | count(PM) | count(Installation) |
+---------+----------------+-----------+---------------------+
|     555 |              0 |         1 |                   0 |
|     444 |              0 |         1 |                   0 |
|     666 |              0 |         0 |                   0 |
|     333 |              0 |         0 |                   0 |
|     222 |              0 |         0 |                   0 |
|     111 |              0 |         0 |                   0 |
+---------+----------------+-----------+---------------------+

我的查询:

 SELECT i.item_ID, 
 COUNT(CASE WHEN i.inc_cat_id = ic.inc_cat_id AND i.inc_cat_id = 1 THEN 1 END) AS cntSupport,
 COUNT(CASE WHEN i.inc_cat_id = ic.inc_cat_id AND i.inc_cat_id = 2 THEN 1 END) AS cntPM,
 COUNT(CASE WHEN i.inc_cat_id = ic.inc_cat_id AND i.inc_cat_id = 3 THEN 1 END) AS cntInstallation
 FROM @incident i
 CROSS APPLY @incCat ic
 WHERE (i.date_logged BETWEEN '2016-04-01' AND '2016-04-30')AND i.cust_id='34'
 GROUP BY i.item_ID

Answer 1

您不需要CROSS APPLY。一个简单的 LEFT JOIN 就可以:

SELECT i.item_id,
       COUNT(CASE WHEN inc.inc_cat_id = 1 THEN 1 END) AS cntSupport,
       COUNT(CASE WHEN inc.inc_cat_id = 2 THEN 1 END) AS cntPM,
       COUNT(CASE WHEN inc.inc_cat_id = 3 THEN 1 END) AS cntInstallation
FROM Item AS i
LEFT JOIN Incident AS inc ON i.item_id = inc.item_id AND
                      inc.date_logged BETWEEN '2016-04-01' AND '2016-04-30'        
WHERE i.cust_id = 34
GROUP BY i.item_id

您只需要从表Item 开始，以便返回所有 项目，就像OP 中的预期结果集一样。

Demo here