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
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_productAnd 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语句
发布评论