SQL Server GROUP BY NULL
我正在使用 SQL Server，并且必须按几列进行分组，但前提是设置为 true。
DECLARE @setting tinyint
SET @setting = 0
SELECT col1 FROM table1
GROUP BY col1,
CASE WHEN @setting = 1 THEN col2 ELSE NULL END
或者我应该默认为 col1，如果设置已设置，我总是使用它进行分组，所以代码将是
CASE WHEN @test = 1 THEN col2 ELSE col1 END
使用 NULL 实际上确实有效，但我无法在互联网上找到示例来证明它是正确的用法。
I am using SQL Server and I have to group by a few columns but only if a setting is true.
DECLARE @setting tinyint
SET @setting = 0
SELECT col1 FROM table1
GROUP BY col1,
CASE WHEN @setting = 1 THEN col2 ELSE NULL END
OR should I default to col1 that I always use to group by if the setting is set, so the code would would be
CASE WHEN @test = 1 THEN col2 ELSE col1 END
It does actually work to use the NULL but I can't find an example on the internet to prove that it is correct usage.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的用法是正确的。你已经知道它有效。
ELSE NULL隐含在 CASE 中，因此您可以将其写为GROUP BY col1, CASE WHEN @setting = 1 THEN col2 END您可以尝试使用诸如
GROUP BY col1, @setting * col2--数字 col2GROUP BY col1, COALESCE(NULLIF(@setting,1), col2)但 CASE 语句实际上展开了一个更好、更简单的计划。
Your usage is correct. You already know it works.
ELSE NULLis implied in CASE, so you could have written it asGROUP BY col1, CASE WHEN @setting = 1 THEN col2 ENDYou could try to be coy with variants like
GROUP BY col1, @setting * col2--for numeric col2GROUP BY col1, COALESCE(NULLIF(@setting,1), col2)But the CASE statement actually unrolls to a better, simpler plan.
你的第一个例子应该可以工作。
为了进一步说明，当
@setting = 1时，您想要group by col1, col2还是group by col1？编辑：你的第一个例子是正确的。
your first example should work.
For further clarification, do you want to
group by col1, col2orgroup by col1when@setting = 1?edit: Your first example is correct.