Mysql When then和temp变量.解释

Question

我正在尝试理解这个sql查询。我可以逐行解释一下吗。我知道第一行初始化变量。第二行得到最小值。

set @r1=0, @r2=0, @r3=0, @r4=0;
select min(Doctor), min(Professor), min(Singer), min(Actor)
from(
select case when Occupation='Doctor' then (@r1:=@r1+1)
when Occupation='Professor' then (@r2:=@r2+1)
when Occupation='Singer' then (@r3:=@r3+1)
when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
case when Occupation='Doctor' then Name end as Doctor,
case when Occupation='Professor' then Name end as Professor,
case when Occupation='Singer' then Name end as Singer,
case when Occupation='Actor' then Name end as Actor
from OCCUPATIONS
order by Name
) Temp
group by RowNumber

Answer 1

变量分别枚举每个职业的行。您可以通过运行子查询来查看结果。

它的作用相当于：

select max(case when occupation = 'Doctor' then name end) as doctor,
max(case when occupation = 'Professor' then name end) as professor,
max(case when occupation = 'Singer' then name end) as singer,
max(case when occupation = 'Actor' then name end) as actor       
from (select o.*,
row_number() over (partition by occupation order by name) as seqnum
from occupations o
) o
group by seqnum;

这是在MySQL 8+中编写查询的更好方法，因为这种使用变量的方法已经被弃用。它正在做的是将职业放在单独的栏中：

occupation     name
doctor         A
doctor         B
singer         C

转化为：

doctor    professor    singer     actor
A                      C
B

中间步骤是：

occupation     name     seqnum
doctor         A           1
doctor         B           2
singer         C           1