sql - 如果 GROUPED 值在源中没有行,SQL Server 如何返回 NULL 而不是 0
问题描述
这是一个包含源数据的 SQL Fiddle,到目前为止我已经尝试过什么,以及我期望得到什么作为输出。http://sqlfiddle.com/#!18/daf90/2
我有一个GameWinnings
表格,其中包含参赛者姓名、他/她参加的回合以及该回合中赢得的金额。
我需要生成一份报告,将参赛者每轮的所有奖金汇总起来。
我坚持的条件是,如果参赛者没有参加过特定回合,那么RoundWinningsAmount
该回合的 应该是NULL
not 0
。我认为CROSS APPLY
or PIVOT
/UNPIVOT
可能会这样做,但还没有确定下来。
这是表格和初始数据,然后是问题陈述和预期输出与我尝试过的结果。
CREATE TABLE dbo.GameWinnings
(
Contestant varchar(100) NOT NULL,
GameRound int NOT NULL,
RoundWinningsAmount numeric(38, 6) NULL
);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Ms Junaiqua',2,33333);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Mr Wang',1,NULL);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Mr Wang',1,NULL);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Mr Wang',1,100);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Mr Wang',2,NULL);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Thad Chad ',1,99);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Thad Chad ',1,1);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Thad Chad ',1,NULL);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Thad Chad ',2,50);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Thad Chad ',2,150);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Thad Chad ',2,NULL);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Thad Chad ',2,NULL);
INSERT INTO dbo.GameWinnings (Contestant,GameRound, RoundWinningsAmount)
VALUES('Thad Chad ',3,300);
CREATE TABLE ExpectedOutput
(
Contestant varchar(100) NOT NULL,
Round_1_Winnings numeric(38, 6) NULL,
Round_2_Winnings numeric(38, 6) NULL,
Round_3_Winnings numeric(38, 6) NULL
);
-- Expected output
INSERT INTO ExpectedOutput VALUES ('Mr Wang', 100 , 0 , NULL)
INSERT INTO ExpectedOutput VALUES ('Ms Junaiqua', NULL , 33333 , NULL)
INSERT INTO ExpectedOutput VALUES ('Thad Chad', 100 , 200 , 300)
如果你做 a SELECT *
,那么这就是数据。
问题陈述和要求
一场比赛有3个回合。所以 GameRound 列的值将永远只有 1 或 2 或 3。
- 由参赛者创建一个结果集,对每一轮获胜的参赛者进行求和
- 如果参赛者没有参加过某一轮,那么该轮的 SUM 值应该是
NULL
,而不是0
所以在上面的示例数据中
- 王先生在第一轮和第二轮都打过,但是第三轮没有。所以第三轮
SUM
应该NULL
没有0
。第2轮SUM
应该是0
因为他在第2轮打过,但没有赢任何钱。 - Junaiqua 女士只参加过第 2 轮,没有参加过第 1 轮或第 3 轮,因此第 1 轮和第 3 轮
SUM
应该NULL
没有0
。 - Thad Chad打了所有 3 轮,所以所有 3 轮都应该
SUM
有价值。
我试过的
-- Current query I've tried to get desired output.
-- The query is returning 0, instead of NULL for Rounds where Contestant didn't participate.
-- I know that this is happening because I am returning 0 in the ELSE of the CASE.
-- Not sure how to fix it.
SELECT
Contestant,
SUM ( CASE WHEN GameRound = 1 THEN RoundWinningsAmount ELSE 0 END) Round_1_Winnings,
SUM ( CASE WHEN GameRound = 2 THEN RoundWinningsAmount ELSE 0 END) Round_2_Winnings,
SUM ( CASE WHEN GameRound = 3 THEN RoundWinningsAmount ELSE 0 END) Round_3_Winnings
FROM dbo.GameWinnings
GROUP BY Contestant
预期产出与实际产出。
红色的值需要是绿色显示的值。
解决方案
您可以使用如下PIVOT查询:
select * from
(
select
RoundWinnings=ISNULL(sum(RoundWinningsAmount),0),
Contestant,
GameRound
from GameWinnings
group by Contestant, GameRound
)src
pivot
(
max(RoundWinnings)
for GameRound in ([1],[2],[3])
)p
推荐阅读
- rust - 原子读取文件内容
- amazon-web-services - 如何让 API 网关将路径转发到下游应用程序?
- database - 消息应用程序数据设计效率不够
- microsoft-teams - 为什么我将 /tab 部署到 azure 时它不起作用?
- python - 你能在 python 中创建一个 twilio twiml bin 吗?
- java - 为什么打印时我的 charAt 给了我一个数字而不是一个字母
- reactjs - 反应路由器和上下文
- kubernetes - 我自己的 kubernetes 运行不会从官方文档中产生相同的结果
- javascript - 带有图像的jquery模态窗口
- tensorflow - 使用 tensorflow 的 OD API 时评估结果低