Mysql的when then_Mysql When then和temp变量。解释

匿名用户

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

它所做的相当于: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;

这是在MySQL8+中编写查询的一种更好的方法，因为不推荐使用这样的变量。它所做的是将职业放在单独的栏中: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