MySQL CASE 基于之前的 CASE 值

Question

在 MySQL 中，是否可以在 SELECT 子句中有两个 CASE 语句，其中第二个 CASE 语句依赖于第一个 CASE 语句？

例如，考虑以下查询:

SELECT CASE WHEN `user`.`id` < 500 THEN 'awesome' ELSE 'lame' END
    AS `status`

     ,  CASE WHEN `status` = 'awesome' THEN 'You rock' ELSE 'You stink' END
    AS `message`

  FROM `user`

基本上，用户 ID 决定状态，然后状态决定消息。

但是，正如您可能已经猜到的那样，这个查询会产生这个错误:

Unknown column 'status'

到目前为止我找到的唯一解决方案是两个生成一个临时表、 View 或子查询，然后消息由这个子查询返回的status决定.

有没有一种方法可以不使用临时表、 View 或子查询来编写此查询？我试图避免使用这些构造，以尽可能保持查询简单和优化。谢谢!

Answer 1

您可以使用临时变量:

select
    @status1 := (case 
        when user.id < 500 then 'awesome' 
        else 'lame' 
    end) as `status`,
    (case 
        when @status1 = 'awesome' then 'You rock' 
        else 'You stink' 
    end) as message
from
    user;

---

关于临时变量你必须知道的一些事情:

1. 它们总是以@ 开头
   • 避免使用保留字，以防万一(这就是我将变量命名为 @status1
   的原因
2. @符号后必须以字母开头，不能有空格
3. 当您在单个查询中更新它们时，它们会“从左到右”(谈论列)和“从头到尾”(谈论行)更新。这可以帮助您计算累计总和或平均值。

示例(针对第 2 点):

select @t := 1, @t := @t + 1;

@t1 | @t2
----+----
1   | 2

示例(第 3 点):

select myTable.x, @t := @t + myTable.x as cummulative_x
from 
    (select @t := 0) as init, -- You need to initialize the variable, 
                              -- otherwise the results of the evaluation will be NULL
    myTable
order by myTable.x -- Always specify how to order the rows,
                   -- or the cummulative values will be quite odd
                   -- (and maybe not what you want)
;

x  | cummulative_x
---+---------------
1  | 1
1  | 2
2  | 4
3  | 7

---

临时变量可以帮助你做一些很棒的事情......随意尝试;)

---

更新

如果你想在这个查询的结果上定义条件，有两种方法可以做到:

1. 将上述查询用作第二个查询的数据源(即，使其成为另一个查询的 from 子句中的子查询
2. 创建一个临时表并对其进行查询

选项 1:

select a.*
from (
    -- The query with temp variables defined
)
where -- ATTENTION: you need to write the references to the column names of the subquery

选项 2:(我个人最喜欢的)

drop table if exists temp_my_temp_table;
create temporary table temp_my_temp_table
    select
        @status1 := (case 
            when user.id < 500 then 'awesome' 
            else 'lame' 
        end) as `status`,
        (case 
            when @status1 = 'awesome' then 'You rock' 
            else 'You stink' 
        end) as message
    from
        user;
-- Add all appropriate indexes to this newly created table:
-- alter table temp_my_temp_table
--     add index idx_status(`status`),
--     add index idx_mess(message);
-- Make your queries on this new temp table
select * from temp_my_temp_table
-- where ...
;

关于临时表你必须知道的事情:

• 它们是在 RAM 上创建的(默认情况下，并且仅当表不是太大时)
• 它们只对创建它的连接可见
• 一旦创建它的连接关闭(或以任何方式终止)，它们就会被消除
• 您不能在 FROM 子句中多次使用它。除此之外，您可以将其用作数据库中的任何其他表

---

另一个更新

我偶然发现了this question and its answer .如果您想使用列的结果(使用临时变量计算)作为条件，MySQL 允许这样做:

select
    @status1 := (case 
        when user.id < 500 then 'awesome' 
        else 'lame' 
    end) as `status`,
    (case 
        when @status1 = 'awesome' then 'You rock' 
        else 'You stink' 
    end) as message
from
    user
having
    `status` = 'awesome';

不要使用 where，而是使用 having，并且不引用临时变量，而是引用列的别名。