MySQL Advanced Select
数据表Occupation保存了每一个人的名字及职业,描述如下:
Column | Type |
Name | string |
Occupation | string |
当没有与职业对应的更多的名称时,打印NULL。
示例数据:
![MySQL Advanced Select - bloveice0915 - 风雨](https://s3.amazonaws.com/hr-challenge-images/12890/1443817648-1b2b8add45-2.png)
Jenny Ashley Meera Jane Samantha Christeen Priya Julia NULL Ketty NULL Mariade
查询语句如下:
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 RowNumers,
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 RowNumers;