MySQL Advanced Select

数据表Occupation保存了每一个人的名字及职业，描述如下：

Column	Type
Name	string
Occupation	string

查询以输出每个名字按字母顺序排列在其相应的职业下面。职业及列标题分别是Doctor，Professor，Singer和Actor。

当没有与职业对应的更多的名称时，打印NULL。

示例数据：

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;

转载于:https://www.cnblogs.com/bloveice0915/p/7659723.html