不能在用于 GROUP BY 子句的 group by 列表的表达式中使用聚合或子查询 [英] Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

问题描述

在下面的sql语句中我得到以下错误

In the below sql statement i get the following error

不能在用于表达式的表达式中使用聚合或子查询按 GROUP BY 子句的列表分组.

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

我该如何解决这个问题?

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

推荐答案

除非您在 GROUP BY 中包含 T.Client，否则您只能将该字段包含在聚合函数.在您的情况下，按该字段分组会更改逻辑，因此就这样了(并且与您按 CASE 语句分组的尝试有关).相反，将 T.Client 包装在聚合函数中.

Unless you include T.Client in 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.Client in an aggregate function.

这样你的组还是一样的，当只有一行时，根据你的 CASE 语句的测试，你知道聚合函数会给出什么结果.

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.

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

这篇关于不能在用于 GROUP BY 子句的 group by 列表的表达式中使用聚合或子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！