如何从另一个表中计数值

How to use count joining with different tables. Please have a look at my queries. Here i am using CROSS APPLY. But i am not getting the actual result. how i can to get all the item from item table not in incident table.

如何使用计数连接不同的表。请看看我的问题。这里我用的是交叉应用。但是我没有得到实际的结果。如何从项目表而不是事件表中获取所有项。

Tabel : Inc_cat

Tabel:Inc_cat

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

Table:incident

表:事件

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

Table:item

表:项目

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

Result:

结果:

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

My Query:

我的查询:

 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

3 个解决方案

#1

You don't need a CROSS APPLY. A simple LEFT JOIN will do:

你不需要交叉应用。简单的左连接即可:

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

You just need to start by table Item, so as to get all items returned, as in the expected result set in the OP.

您只需要从表项开始，以便获得返回的所有项，如OP中的预期结果集。

Demo here

演示

#2

Unless I'm missing something, your query is over complicated:

除非我漏掉了什么，否则你的问题太复杂了:

SELECT item_ID, 
       COUNT(CASE WHEN .inc_cat_id = 1 THEN 1 END) AS cntSupport,
       COUNT(CASE WHEN inc_cat_id = 2 THEN 1 END) AS cntPM,
       COUNT(CASE WHEN inc_cat_id = 3 THEN 1 END) AS cntInstallation
  FROM @incident 
 GROUP BY item_ID

#3

UPDATE:

更新:

With the addition of logged_date column, you just need to add it in the ON clause. You need to use sp_executesql instead of EXEC now to prevent sql injection:

添加logged_date列之后，只需要在ON子句中添加它。现在需要使用sp_executesql而不是EXEC来防止sql注入:

DECLARE @fromDate DATE = '2016-04-01',
        @toDate DATE = '2016-04-30';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql =
'SELECT
    i.item_id' + CHAR(10) +
(SELECT
'   , COUNT(CASE WHEN inc.inc_cat_id = ' + CONVERT(VARCHAR(10), inc_cat_id) + 
' THEN 1 END) AS ' + QUOTENAME('count(' + inc_cat_n + ')') + CHAR(10)
FROM #Inc_cat
ORDER BY inc_cat_id
FOR XML PATH('')
) +
'FROM #item AS i
LEFT JOIN #incident AS inc
    ON i.item_id = inc.item_id
    AND inc.date_logged BETWEEN @fromDate AND @toDate
GROUP BY i.item_id;';

PRINT (@sql);
EXEC sp_executesql
        @sql,
        N'@fromDate DATE, @toDate DATE',
        @fromDate,
        @toDate

ONLINE DEMO

在线演示

---

Giorgos answer is good if you only have that 3 Inc_cats. However, if you have unknown number of Inc_cats, you need to do it dynamically. Here is a method using a dynamic crosstab:

如果你只有3只猫，乔尔戈斯的回答是好的。但是，如果您有未知数量的Inc_cats，您需要动态地执行它。这里有一个使用动态交叉表的方法:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql =
'SELECT
    i.item_id' + CHAR(10) +
(SELECT
'   , COUNT(CASE WHEN inc.inc_cat_id = ' + CONVERT(VARCHAR(10), inc_cat_id) + 
' THEN 1 END) AS ' + QUOTENAME('count(' + inc_cat_n + ')') + CHAR(10)
FROM Inc_cat
ORDER BY inc_cat_id
FOR XML PATH('')
) +
'FROM Item AS i
LEFT JOIN Incident AS inc
    ON i.item_id = inc.item_id
GROUP BY i.item_id;';

PRINT (@sql);
EXEC (@sql);

ONLINE DEMO

在线演示

Basically, it's just a dynamic version of Giorgos' answer.

基本上，这只是乔尔哥斯回答的一个动态版本。