计算临时表中的空值(Counting null values in a temporary table)

计算临时表中的空值(Counting null values in a temporary table)

我有一个存储过程，在其中创建一个临时表。 此表有几列，其中一列只能有五个不同的值，具体取决于某些条件。

myID | ... | myValues - - - |- - -|- - - - - - - 1 | ... | Message 1 2 | ... | Message 2 3 | ... | Message 3 4 | ... | Message 4 5 | ... | Message 5 6 | ... | Message 2 7 | ... | Message 4

现在我有另一个存储过程来读取此表并返回每个消息在表上显示的次数。

SELECT myValues, COUNT(*) AS Nr FROM #temp_table GROUP BY myValues

Output:

myValues | Nr - - - - - -|- - - Message 1 | 1 Message 2 | 2 Message 3 | 1 Message 4 | 2 Message 5 | 1

问题是当我没有这些消息时。 我的查询只会计算表格中的项目，但我希望它们的值为零。

myID | ... | myValues - - - |- - -|- - - - - - - 1 | ... | Message 1 2 | ... | Message 3 3 | ... | Message 3 --# WANTED OUTPUT #-- myValues | Nr - - - - - -|- - - Message 1 | 1 Message 2 | 0 Message 3 | 2 Message 4 | 0 Message 5 | 0

我尝试使用连接，我正在考虑创建另一个表，但我不知道如何使这个工作。

I have a Stored Procedure where it is created a temporary table. This table has several columns and one of them can only have five different values, depending on some conditions.

myID | ... | myValues - - - |- - -|- - - - - - - 1 | ... | Message 1 2 | ... | Message 2 3 | ... | Message 3 4 | ... | Message 4 5 | ... | Message 5 6 | ... | Message 2 7 | ... | Message 4

Now I have another Stored Procedure that reads this table and returns the number of times that each message is shown on the table.

SELECT myValues, COUNT(*) AS Nr FROM #temp_table GROUP BY myValues

Output:

myValues | Nr - - - - - -|- - - Message 1 | 1 Message 2 | 2 Message 3 | 1 Message 4 | 2 Message 5 | 1

The problem is when I don't have any one of these messages. My query will only count the items on the table, but I want them to be there with a zero value.

myID | ... | myValues - - - |- - -|- - - - - - - 1 | ... | Message 1 2 | ... | Message 3 3 | ... | Message 3 --# WANTED OUTPUT #-- myValues | Nr - - - - - -|- - - Message 1 | 1 Message 2 | 0 Message 3 | 2 Message 4 | 0 Message 5 | 0

I tried using joins, and I was thinking about creating another table but I don't know how to get this working.

最满意答案

假设您事先知道所有可能的值，您可以加入它们：

SELECT a.myValues, COALESCE(cnt, 0) AS Nr FROM (SELECT 'Message 1' AS myValues UNION ALL SELECT 'Message 2' UNION ALL SELECT 'Message 3' UNION ALL SELECT 'Message 4' UNION ALL SELECT 'Message 5') a LEFT JOIN (SELECT myValues, COUNT(*) AS cnt FROM #temp_table GROUP BY myValues) b ON a.myValues = b.myValues

Assuming you know all the possible values upfront, you could join on them:

SELECT a.myValues, COALESCE(cnt, 0) AS Nr FROM (SELECT 'Message 1' AS myValues UNION ALL SELECT 'Message 2' UNION ALL SELECT 'Message 3' UNION ALL SELECT 'Message 4' UNION ALL SELECT 'Message 5') a LEFT JOIN (SELECT myValues, COUNT(*) AS cnt FROM #temp_table GROUP BY myValues) b ON a.myValues = b.myValues