We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
此处max实际上是在筛选非null项，所以换成min效果也是一样的，后文中group by rownum，产生了二维的数据，但实际上只有对角线上有非null值，此处是为了降维
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
Name,
Occupation,
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum
FROM OCCUPATIONS
) AS Sub
GROUP BY RowNum
ORDER BY RowNum;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Occupations
You are viewing a single comment's thread. Return to all comments →
此处max实际上是在筛选非null项，所以换成min效果也是一样的，后文中group by rownum，产生了二维的数据，但实际上只有对角线上有非null值，此处是为了降维 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 Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum FROM OCCUPATIONS ) AS Sub GROUP BY RowNum ORDER BY RowNum;