英文:
CASE WHEN in GROUP BY SQL Expression
问题
Q1:
当[A]为空时选择[B]，否则选择[A]，
总和([X])
从TABLE_ABX
按case when [A] is null then [B] else [A] end分组;
Q2:
当[A]为空时选择[B]，否则选择[A]，
总和([X])
从TABLE_ABX
按[A], [B]分组;
这两个SQL查询是否始终等效？这更多是一个理论性问题。目前没有尝试任何特定的查询。
英文:
I have this simple question: given Query 1 (Q1) and Query 2 (Q2):
Q1:
select case when [A] is null then [B] else [A] end,
sum([X])
from TABLE_ABX
group by case when [A] is null then [B] else [A] end;
Q2:
select case when [A] is null then [B] else [A] end,
sum([X])
from TABLE_ABX
group by [A], [B];
Would those two SQL queries always be equivalent?
It's more of theoretical question. Not trying any specific query at the moment.
答案1
得分: 4
查询不等价。用一个反例展示这一点最简单：
| a | b | x |
|---|---|---|
| 1 | 1 | 10 |
| 1 | 2 | 20 |
由于 b 从不为 null，表达式 case when [A] is null then [B] else [A] end 总是返回 a，或者对于两行都是 1。
在第一个查询中，由于这个表达式在 group by 子句中，结果中将得到一行，总和为 30。
在第二个查询中，由于将 a 和 b 的组合进行分组，结果中将得到两行，一行为 10，另一行为 20。
英文:
The queries are not equivalent. The easiest way to show this with a counter-example:
| a | b | x |
|---|---|---|
| 1 | 1 | 10 |
| 1 | 2 | 20 |
Since b is never null, the expression case when [A] is null then [B] else [A] end always returns a, or 1 for both rows.
In the first query, since this expression is in the group by clause, you'll get one row in the result, with a sum of 30.
In the second query, since the combination of a and b is grouped by, you'll get two rows in the result, one with 10 and one with 20.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论