代码之家 › 专栏 › 技术社区 › Ryan Gadsdon

要在同一行SQL Server上显示的Case语句

sql-server sql

Ryan Gadsdon · 技术社区 · 7 年前

我真的很难回答一个问题。

当前查询为

SELECT 
min(ID),
Date, 
CASE WHEN (ID) = '111' then SUM(Column1) END AS [Outcome1],
CASE WHEN (ID) = '222' then SUM(Column1) END AS [Outcome2],
CASE WHEN (ID) = '333' then SUM(Column1) END AS [Outcome3],
CASE WHEN (ID) = '444' then SUM(Column1) END AS [Outcome4]
FROM Table1
GROUP BY ID, Date

结果产生

|  ID |     Date | Outcome1 | Outcome2 | Outcome3 | Outcome4 |
|-----|----------|----------|----------|----------|----------|
| 111 | 01/01/18 |       20 |   (null) |   (null) |   (null) |
| 222 | 01/01/18 |   (null) |       20 |   (null) |   (null) |
| 333 | 01/01/18 |   (null) |   (null) |       20 |   (null) |
| 444 | 01/01/18 |   (null) |   (null) |   (null) |       20 |

然而,我想要的结果是

|  ID |     Date | Outcome1 | Outcome2 | Outcome3 | Outcome4 |
|-----|----------|----------|----------|----------|----------|
| 111 | 01/01/18 |       20 |       20 |       20 |       20 |

我尝试过分组,但最终的结果是80分,结果只有1分。但我希望它与4个结果列一起显示。

有人能帮我吗?谢谢

SQL小提琴- http://sqlfiddle.com/#!18/9516c/2

1 回复 | 直到 7 年前

Pà®°à®¤à¯à®ªà¯ 7 年前

申请 SUM 顶部骨料 case 陈述

SELECT 
min(ID),
Date, 
SUM(CASE WHEN (ID) = '111' then Column1 END) AS [Outcome1],
SUM(CASE WHEN (ID) = '222' then Column1 END) AS [Outcome2],
SUM(CASE WHEN (ID) = '333' then Column1 END) AS [Outcome3],
SUM(CASE WHEN (ID) = '444' then Column1 END) AS [Outcome4]
FROM Table1
GROUP BY Date

同时拆下 ID 从…起 group by