如何使用 CASE 执行 SQL 请求 [英] How to do an SQL request using CASE

问题描述

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

I'm trying to do an SQL request using a clause case, but always it return this error:

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 是一个 date.

I'm using MySQL. Facturation is a date.

谁能告诉我我的错在哪里?谢谢.

Can someone please tell me me where's my fault ? Thank you.

推荐答案

问题太多，不知道从何说起.

There are so many things wrong, it's hard to know where to begin.

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

You're mixing up the two forms of CASE expression. One form is:

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

另一个是:

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

您正在尝试将 SELECT 查询用作值，但缺少 FROM 子句，您必须将查询括在括号中才能将其用作价值.我怀疑您希望从同一个表中查询，在这种情况下，您不应该执行子查询，而应该只在主查询中使用聚合函数.

You're trying to use a SELECT query as a value, but it's missing the FROM clause and you have to wrap a query in parentheses to use it as a value. I suspect you wanted this to be querying from the same table, in which case you shouldn't be doing a subquery, you should just use the aggregation function in the main query.

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

The CASE expression should be part of the SELECT list, not after the FROM clause.

如果您想在输出中为每种情况创建单独的列，它们不能在一个 CASE 表达式中.

If you want to create separate columns in the output for each case, they can't be in one CASE expression.

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

You have all your table and column names in double quotes, MySQL uses backticks to quote names.

使用 GROUP BY 时不需要 SELECT DISTINCT.

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

You can't refer to an alias in the SELECT list in the same query, except in GROUP BY, ORDER BY, and HAVING.

应该是:

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

这篇关于如何使用 CASE 执行 SQL 请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！