问题描述
CASE
当col1> col2 THEN SUM(col3 * col4)
ELSE 0
END as some_product
我想把它放在我的GROUP BY子句中,但这似乎会导致问题,因为列中有一个聚合函数。在这种情况下,有没有办法将GROUP BY列别名,如 some_product
,或者我需要把它放在子查询和组中?
我的猜测是您并不真正想要 GROUP BY
some_product。
答案:是否有一种方法可以为 GROUP BY
列别名例如在这种情况下的some_product,还是我需要把它放在子查询和组中? 是:您不能 GROUP BY
列别名。 $ b $ SELECT
子句,其中列别名被分配,直到 GROUP BY
子句。内联视图或公用表表达式(CTE)可用于将结果用于分组。
$ b
b
select ...
from(select ...,CASE WHEN col1> col2 THEN SUM(col3 * col4)ELSE 0 END as some_product
从...
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 ...
I have a column that looks something like this:
CASE
WHEN col1 > col2 THEN SUM(col3*col4)
ELSE 0
END AS 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?
My guess is that you don't really want to GROUP BY
some_product.
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.
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.
Inline view:
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 ...
这篇关于具有聚合函数的SQL GROUP BY CASE语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!