中删除
im在查询方面遇到了真正的困难。
当前查询是
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
在case
语句
SUM
汇总 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
还从group by
ID