乘以在CASE语句中计算出的两列 [英] Multiplying two columns which have been calculated on a CASE statement

问题描述

我在PostgreSQL中使用 CASE 语句执行SQL查询，如下所示：

I am performing a SQL query in PostgreSQL using a CASE statement like this:

SELECT
    CASE column1
        WHEN something THEN 10
        ELSE 20
        END AS newcol1
    CASE column12
        WHEN something THEN 30
        ELSE 40
        END AS newcol2
COUNT(column3) newcol3
FROM table
GROUP BY newcol1,newcol2,newcol3

我需要第四列，它必须是 newcol2 * newcol3 的结果，我该怎么做？

I need a fourth column which has to be the result of newcol2 * newcol3, how can I do that?

如果我把（newcol2 * newcol3）作为newcol4 ，我得到一个语法错误。

If I put (newcol2 * newcol3) AS newcol4 I get a syntax error.

推荐答案

您可以随时使用CTE将事物抽象到不同的级别，如果这有帮助的话 - 沿着...

You can always use a CTE to abstract things away to a different level, if that helps - something along the lines of ...

With CTE as
(
 SELECT
  CASE column1
    WHEN something THEN 10
    ELSE 20
    END AS newcol1,
  CASE column12
    WHEN something THEN 30
    ELSE 40
    END AS newcol2,
  column3,
 FROM table
)
SELECT
  newcol1, newcol2,
  count(column3) as newcol3,
 (newcol2 * newcol3) AS newcol4
FROM CTE 
GROUP BY newcol1,newcol2,newcol3

这篇关于乘以在CASE语句中计算出的两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！