乘以已计算的CASE语句两列(Multiplying two columns which have

我使用的是PostgreSQL中执行SQL查询CASE这样的语句：

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 ，我该怎么办呢？

如果我把(newcol2 * newcol3) AS newcol4我得到一个语法错误。

Answer 1:

您可以随时使用CTE抽象东西拿走，以不同的级别，如果这能帮助 - 沿着线的东西...

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

Answer 2:

热膨胀系数是一种有效的方法，让更多的选项。
对于一个简单的情况下，这样一个简单的子查询更简单，速度稍快。

SELECT *, (newcol2 * newcol3) AS newcol4
FROM  (
   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) AS newcol3
   FROM  table
   GROUP BY 1, 2
   ) AS sub

BTW：我取出newcol3从GROUP BY ，因为你正在运行的聚合函数count()就可以了，这是稍微荒谬的。

文章来源: Multiplying two columns which have been calculated on a CASE statement