时间戳

首页 > 解决方案 > 返回 GROUP BY CASE WHEN IS NULL THEN (...) ELSE (...) 的行，即使记录不存在

sql - 返回 GROUP BY CASE WHEN IS NULL THEN (...) ELSE (...) 的行，即使记录不存在

问题描述

让我们考虑以下场景。

CREATE TABLE Replicant (Name NVARCHAR(10),Gen INT);
INSERT INTO Replicant VALUES ('tymtam', 2), ('Roy', 6);

SELECT   
    CASE WHEN Gen < 10 THEN '<10' ELSE '>=10' END as 'Gen', 
    count(*) as 'Count' 
FROM Replicant
GROUP BY CASE WHEN Gen < 10 THEN '<10' ELSE '>=10' END;

结果是一行：

Gen Count
<10 2

我可以提高查询的复杂性，以便在 ELSE 案例中得到零吗？

Gen  Count
<10  2
>=10 0

更新 2

我的鉴别器是'为空'

SELECT   CASE WHEN Gen IS NOT NULL THEN 'Known' ELSE 'Unknown' END as 'Gen', count(*) as 'Count' FROM Replicant
GROUP BY CASE WHEN Gen IS NOT NULL THEN 'Known' ELSE 'Unknown' END;

结果是

Gen     Count
Known   2

我渴望

Gen     Count
Known   2
Unknown 0

更新 1

我的上下文是我有不同代复制者的查询（指标）对：

INSERT INTO [dbo].[Metrics] (...) SELECT
'Metric X for >=10' as 'Name',
COUNT(*) AS 'Count',
(80_char_expression) AS 'Sum',
(80_char_expression) AS 'Min',
(80_char_expression) AS 'Max', 
0 AS 'StandardDeviation'
FROM Replicant  
WHERE TimestampUtc > DATEADD(WEEK, -1, Current_Timestamp)
AND Gen >= 10

INSERT INTO [dbo].[Metrics] (...) SELECT
'Metric X for <10' as 'Name',
--7 lines repeated from the 1st query
AND Gen < 10

即使没有记录，我也希望有一个选择来插入两行。

标签： sqltsql

解决方案

您可以尝试使用UNOIN ALL为您的分数制作比较表，然后执行outer join

查询 1：

SELECT t1.word,
       COUNT(Name) 'Count'
FROM
(
SELECT '<10' word,9 maxval,0 minval
UNION ALL
SELECT '>=10' word,2147483646 maxval,10 minval
) t1 LEFT JOIN Replicant on Gen BETWEEN t1.minval AND t1.maxval
GROUP BY t1.word

结果：

| word | Count |
|------|-------|
|  <10 |     2 |
| >=10 |     0 |

推荐阅读