带聚合函数的 SQL GROUP BY CASE 语句
我有一列看起来像这样:
I have a column that looks something like this:
CASE
WHEN col1 > col2 THEN SUM(col3*col4)
ELSE 0
END AS some_product
我想把它放在我的 GROUP BY 子句中，但这似乎会导致问题，因为列中有一个聚合函数.在这种情况下，有没有办法 GROUP BY 列别名，例如 some_product，或者我是否需要将它放在子查询中并对其进行分组?
And I would like to put it in my GROUP BY clause, but this seems to cause problems because there is an aggregate function in column. Is there a way to GROUP BY a column alias such as some_product in this case, or do I need to put this in a subquery and group on that?
推荐答案
我猜你不是真的想要GROUP BY some_product.
My guess is that you don't really want to GROUP BY some_product.
回答:在这种情况下，有没有办法GROUP BY列别名，例如 some_product，或者我是否需要将其放在子查询中并组那个?"是:您不能GROUP BY列别名.
The answer to: "Is there a way to GROUP BY a column alias such as some_product in this case, or do I need to put this in a subquery and group on that?" is: You can not GROUP BY a column alias.
分配列别名的 SELECT 子句在 GROUP BY 子句之后才会处理.可以使用内联视图或公用表表达式 (CTE) 使结果可用于分组.
The SELECT clause, where column aliases are assigned, is not processed until after the GROUP BY clause. An inline view or common table expression (CTE) could be used to make the results available for grouping.
内联视图:
select ...
from (select ... , CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product
from ...
group by col1, col2 ... ) T
group by some_product ...
CTE:
with T as (select ... , CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product
from ...
group by col1, col2 ... )
select ...
from T
group by some_product ...
相关文章