mysql - 如何使用 CASE 执行 SQL 请求

Question

我正在尝试使用子句case 执行SQL 请求，但它总是返回此错误:

ERROR: syntax error at or near "CASE"

我的要求如下:

SELECT distinct extract(month from "Facturation") as month, "LRU", "Client"

from "foundry_sync"."data"

    CASE month
  WHEN "month" =1 THEN 
    select avg("Montant_fac_eur") as c1 where "Facturation" between 1 and 6

 when "month" =2 THEN 
 select avg("Montant_fac_eur") as c2 where "Facturation" between 2 and 7

 when "month" =3 THEN
 select avg("Montant_fac_eur") as c3 where "Facturation" between 3 and 8

 when "month" = 4 then 
 select avg("Montant_fac_eur") as c4 where "Facturation" between 4 and 9

 when "month"=5 THEN
 select avg("Montant_fac_eur") as c5 where "Facturation" between 5 and 10

 when "month"=6 THEN 
 select avg("Montant_fac_eur") as c6 where "Facturation" between 6 and 11

 when "month"=7 THEN
 select avg("Montant_fac_eur") as c7 where "Facturation" between 7 and 11

 else ''

END 

group by "LRU", "Client", "Facturation"
order by "Client", "month"

我正在使用 MySQL。Facturation 是一个日期。

有人可以告诉我我的错在哪里吗？谢谢。

Answer 1

有太多错误，很难知道从哪里开始。

您混淆了 CASE 表达式的两种形式。一种形式是:

CASE <expression>
    WHEN <value> THEN <result>
    WHEN <value> THEN <result>
    ...
END

另一个是:

CASE
    WHEN <condition> THEN <result>
    WHEN <condition> THEN <result>
    ...
END

您正在尝试使用 SELECT 查询作为值，但它缺少 FROM 子句，您必须将查询括在括号中才能将其用作值(value)。我怀疑你希望这是从同一个表查询，在这种情况下你不应该做子查询，你应该只在主查询中使用聚合函数。

CASE 表达式应该是 SELECT 列表的一部分，而不是在 FROM 子句之后。

如果您想在每个案例的输出中创建单独的列，它们不能在一个 CASE 表达式中。

所有表名和列名都用双引号引起来，MySQL 使用反引号来引用名称。

使用 GROUP BY 时不需要 SELECT DISTINCT。

您不能在同一查询中引用 SELECT 列表中的别名，GROUP BY、ORDER BY 和拥有。

应该是:

SELECT MONTH(Facturation) AS month, LRU, Client,
    AVG(CASE WHEN MONTH(Factuation) = 1 AND Facturation BETWEEN 1 AND 6
        THEN Montant_fac_eur END) AS c1,
    AVG(CASE WHEN MONTH(Factuation) = 2 AND Facturation BETWEEN 2 AND 7
        THEN Montant_fac_eur END) AS c2,
    AVG(CASE WHEN MONTH(Factuation) = 3 AND Facturation BETWEEN 3 AND 8
        THEN Montant_fac_eur END) AS c3,
    AVG(CASE WHEN MONTH(Factuation) = 4 AND Facturation BETWEEN 4 AND 9
        THEN Montant_fac_eur END) AS c4,
    ...
FROM foundry_sync.data
GROUP BY `LRU`, `Client`, `Facturation`
ORDER BY Client, month