基于先前案例值的MySQL案例

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'

到目前为止，我找到的唯一解决方案是生成一个临时表、视图或子查询，然后message由该子查询中返回的status确定。

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

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的原因

​

1. 在@符号之后，它们必须以字母开头，不能有空格。
2. 当您在单个查询中更新它们时，它们将被更新为“从左到右”(谈论列)和“第一次到最后”(讨论行)。这可以帮助您计算累积和或平均值。

示例(用于第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，而不是引用临时变量，而是引用列的别名。