数据透视和分区查询以获取名称

Question

我尝试了下面的HackerBank问题，其中名称必须以职业为中心。

https://www.hackerrank.com/challenges/occupations/problem

我找到了一个解决方案，但不明白它是如何工作的，特别是为什么在对名称取最大值时，其他名称会返回结果。

表：

CREATE TABLE Occupations
(
Name varchar(25),
Occupation varchar(1000)
)
INSERT INTO Occupations VALUES('Samantha', 'Doctor')
GO
INSERT INTO Occupations VALUES('Julia', 'Actor')
GO
INSERT INTO Occupations VALUES('Maria', 'Actor')
GO
INSERT INTO Occupations VALUES('Meera', 'Singer')
GO
INSERT INTO Occupations VALUES('Ashley', 'Professor')
GO
INSERT INTO Occupations VALUES('Ketty', 'Professor')
GO
INSERT INTO Occupations VALUES('Christeen', 'Singer')
GO
INSERT INTO Occupations VALUES('Jane', 'Actor')
GO
INSERT INTO Occupations VALUES('Jenny', 'Doctor')
GO
INSERT INTO Occupations VALUES('Priya', 'Singer')
GO

解决方案：

SELECT [Doctor], [Professor], [Singer],[Actor] 
FROM 
(   
SELECT ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name ASC) AS RowNumber, * 
FROM Occupations) as tempTable 
PIVOT 
(
MAX(Name) FOR OCCUPATION IN ([Doctor], [Professor], [Singer], [Actor])
) AS pivotTable

因此，我理解这个查询执行的方式是，行按名称排序的Occupation进行分区，然后最大名称以Occupations为中心。那么，查询如何仍然按职业返回其他名称呢？

Answer 1

/使用MS SQL服务器/

SELECT [Doctor],[Professor],[Singer],[Actor] from (select row_number() over (partition by occupation order by Name)rownumber,name,occupation from occupations group by occupation,name)as table2 pivot(max(name) for occupation in ([Doctor],[professor],[Singer],[Actor])) as tab1 order by rownumber

Answer 2

select
Doctor,
Professor,
Singer,
Actor
from (
select
NameOrder,
max(case Occupation when 'Doctor' then Name end) as Doctor,
max(case Occupation when 'Professor' then Name end) as Professor,
max(case Occupation when 'Singer' then Name end) as Singer,
max(case Occupation when 'Actor' then Name end) as Actor
from (
select
Occupation,
Name,
row_number() over(partition by Occupation order by Name ASC) as NameOrder
from Occupations
) as NameLists
group by NameOrder
) as Names