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;