SQL 不能在用于 GROUP BY 子句的 group by 列表的表达式中使用聚合或子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9873646/
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
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause
提问by AzaRoth91
In the below sql statement i get the following error
在下面的 sql 语句中，我收到以下错误
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.
不能在用于 GROUP BY 子句的 group by 列表的表达式中使用聚合或子查询。
How can i get around this?
我怎样才能解决这个问题？
SELECT
T.Post,
COUNT(*) AS ClientCount,
Client = CASE COUNT(*) WHEN '1' THEN T.Client ELSE '[Clients]' END
FROM
MyTable T
GROUP BY
T.Post,
CASE COUNT(*) WHEN '1' THEN T.Client ELSE '[Clients]' END
回答by MatBailie
Unless you include T.Clientin your GROUP BY, you can only include that field within an aggregate function. In your case, grouping by that field changes the logic, so that's out (and is related to your attempt to group by the CASE statement). Instead, wrap T.Clientin an aggregate function.
除非您包含T.Client在您的 中GROUP BY，否则您只能在聚合函数中包含该字段。在您的情况下，按该字段分组会更改逻辑，因此就这样了（并且与您按 CASE 语句分组的尝试有关）。相反，包装T.Client在聚合函数中。
This way your groups are still the same, and when there is only one row, as per your CASE statement's test, you know what result the aggregate funciton is going to give.
这样你的组仍然是一样的，当只有一行时，根据你的 CASE 语句的测试，你知道聚合函数会给出什么结果。
SELECT
T.Post,
ClientCount = COUNT(*) AS ClientCount,
Client = CASE COUNT(*) WHEN 1 THEN MAX(T.Client) ELSE '[Clients]' END
FROM
MyTable T
GROUP BY
T.Post
回答by Tomalak
You do not need to group by that CASE expression.
您不需要按该 CASE 表达式进行分组。
SELECT
T.Post,
COUNT(*) AS ClientCount,
CASE COUNT(*) WHEN '1' THEN MIN(T.Client) ELSE '[Clients]' END Client
FROM
MyTable T
GROUP BY
T.Post
