SQL 将 case 与聚合函数和 group by 子句一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14581259/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using case with aggregate function & group by clauses
提问by galeka
I have a query using CASE with aggregate function and group by clause, like this
我有一个使用带有聚合函数和 group by 子句的 CASE 查询，就像这样
SELECT
A
,B
,C
,CASE
WHEN <COLUMN_NAME_A> IS NOT NULL
THEN (SUM(<COLUMN_NAME_B>) * <COLUMN_NAME_A>)
ELSE 0
END AS <ALIAS>
FROM <TARGET_TABLE>
GROUP BY
A
,B
,C
,<ALIAS>
and I got an error "GROUP BY and WITH...BY clauses may not contain aggregate"
我收到一个错误“GROUP BY 和 WITH...BY 子句可能不包含聚合”
then, I change my script (exclude from group by)
然后，我更改我的脚本（从 group by 中排除）
SELECT
A
,B
,C
,CASE
WHEN <COLUMN_NAME_A> IS NOT NULL
THEN (SUM(<COLUMN_NAME_B>) * <COLUMN_NAME_A>)
ELSE 0
END AS <ALIAS>
FROM <TARGET_TABLE>
GROUP BY
A
,B
,C
and still got an error "Selected non-aggregate values must be part of the associated group"
仍然出现错误“选定的非聚合值必须是关联组的一部分”
please help me to understand why must be part of group by, and what do I have to do to fix it?
请帮助我理解为什么必须成为 group by 的一部分，我该怎么做才能解决这个问题？
回答by Hamlet Hakobyan
Try this:
尝试这个：
SELECT
A
,B
,C
,SUM(CASE
WHEN <COLUMN_NAME_A> IS NOT NULL
THEN <COLUMN_NAME_B> * <COLUMN_NAME_A>
ELSE 0
END) AS <ALIAS>
FROM <TARGET_TABLE>
GROUP BY
A
,B
,C
But your CASE statement have no sense. If <COLUMN_NAME_A>is NULLi.e. <COLUMN_NAME_B> * <COLUMN_NAME_A>is NULLit will be ignored by SUMaggregate.
Use this instead.
但是您的 CASE 声明没有任何意义。如果<COLUMN_NAME_A>是NULL即<COLUMN_NAME_B> * <COLUMN_NAME_A>是NULL它将被SUM聚合忽略。改用这个。
SELECT
A
,B
,C
,ISNULL(SUM(<COLUMN_NAME_B> * <COLUMN_NAME_A>), 0) AS <ALIAS>
FROM <TARGET_TABLE>
GROUP BY
A
,B
,C
ISNULLused in case when no non-null value retrieved.
ISNULL在没有检索到非空值的情况下使用。
回答by Yunqing Xiao
Maybe try this
也许试试这个
SELECT A
,B
,C
,CASE
WHEN <COLUMN_NAME_A> IS NOT NULL
THEN (SUM(<COLUMN_NAME_B>) * <COLUMN_NAME_A>)
ELSE 0
END AS <ALIAS>
FROM <TARGET_TABLE>
GROUP BY
A
,B
,C
,<COLUMN_NAME_A>
