LeetCode-618. 学生地理信息报告（困难）行转列

一所美国大学有来自亚洲、欧洲和美洲的学生，他们的地理信息存放在如下 student 表中。

| name   | continent |
|--------|-----------|
| Jack   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jane   | America   |

写一个查询语句实现对大洲（continent）列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。数据保证来自美洲的学生不少于来自亚洲或者欧洲的学生。

对于样例输入，它的对应输出是：

| America | Asia | Europe |
|---------|------|--------|
| Jack    | Xi   | Pascal |
| Jane    |      |        |

进阶：如果不能确定哪个大洲的学生数最多，你可以写出一个查询去生成上述学生报告吗？

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/students-report-by-geography
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

审题：根据原表写一个查询，生成新的结果。

数据保证来自美洲的不少于来自亚洲和欧洲的学生人数。所以排序可根据人数排出美洲，然后根据首字母排出亚欧州。

思考：查询相对简单，需要排序，和保证美洲人数多。

解题：

解法一

按continent字段分组，聚合，组内排序。可得。

+--------+-----------+
| name   | continent |
+--------+-----------+
| Jack   | America   |
| Jane   | America   |
| Xi     | Asia      |
| Pascal | Europe    |
+--------+-----------+

现在要行转列。

用America,Asia,Europe作为属性，Jack,Jane,Xi,Pascal作为值。

确定 Jack,Jane,Xi,Pascal 所在的数据行。

从 America 属性看，Jack在第1行，Jane在第2行；

从 Asia属性看，Xi在第1行。

从Europe属性看，Pascal在第1行。

最后一步，确定每个数据行中的数据。

按照 America,Asia,Europe 的顺序，

第1行：Jack Xi Pascal

第2行：Jane NULL NULL

合起来得出结果为：

| America | Asia | Europe |
|---------|------|--------|
| Jack    | Xi   | Pascal |
| Jane    |      |        |

从上面的思路看，重点是两步：

第一，确定每个属性中各个数据所在的数据行。 其实是求每个数据的排名。

第二，确定每个数据行都有哪些数据。按照排名放置数据。

求每个数据的排名

求排名通常有两种方法，表自连接和用户变量法。

先讲表自连接法求排名

本题的输入表中，可能会存在多行相同的(name,continent)。

比如：

...
(A,B)
(A,B)
(A,B)
...

那么，仅用两个字段，用表自连接，无法确定每个(A,B)的排名。

将数据的行号作为第三个字段，用于区分相同的数据。

定义用户变量：@row_id——数据的行号，从1开始。

(SELECT @row_id:=0) AS T

给数字增加行号，结果表命名为S1：

(
	SELECT 
        S.*,
        @row_id:=(@row_id + 1) AS `row_id`
	FROM student AS S,(SELECT @row_id:=0) AS T
) AS S1

S1表自连接算排名。

在同一个洲中，对每个人A，找出所有人B，满足条件：A.name > B.name 或 A.name = B.name 且 A.row_id > B.row_id。

意思是name字典序小的人排名在前，name字典序相同的人，行号小的排名在前。

S1表自连接也分join和left join。

用join，并且 “A.row_id > B.row_id ”改为“ A.row_id >= B.row_id ”。每个人都有排名，且排名从1开始。

用left join，每个人的排名从0开始。

此处用join，算排名的逻辑为：

SELECT S1.continent,S1.NAME,S1.row_id,COUNT(*) AS `trank`
FROM 
(
	SELECT S.*,@row_id:=(@row_id + 1) AS `row_id`
	FROM student AS S,(SELECT @row_id:=0) AS T
) AS S1 
JOIN 
(
	SELECT S.*,@n_row_id:=(@n_row_id + 1) AS `n_row_id`
	FROM student AS S,(SELECT @n_row_id:=0) AS T
) AS S2 
	ON (S1.continent = S2.continent AND (S1.NAME > S2.NAME OR (S1.NAME = S2.NAME AND S1.row_id >= S2.n_row_id)))
group BY S1.continent,S1.NAME,S1.row_id
order BY S1.continent,S1.NAME

尽管是S1表自连接，却引入了S2表。因为表名要唯一。

另外S2中的row_id也改为n_row_id。两者值相等。由于mysql中，S1表中的用户变量@row_id，会在表S2中被共享。如果在表S2中继续用@row_id表示行号，其值显然不对。才新增了变量@n_row_id作为行号。

此外，group by子句中，分组条件是：S1.continent,S1.NAME,S1.row_id。

因为要确定每个人的排名，分组依据应该是每个唯一的人。仅用 S1.continent,S1.NAME 不能唯一确定每个人，必须带上row_id。这才是前面引入row_id的意义。

再讲用户变量法求排名

用户变量法则相对简单。按continent升序，再按name升序。同一continent内，按name从小到大，排名从1开始。

用户变量：@trank——排名。@pre_con——前一行的continent。

排名逻辑如下，结果命名为表A

(
	SELECT S.*,
	@trank:=if(@pre_con = S.continent,
		@trank + 1,
		1
	) AS `trank`,
	@pre_con:=S.continent AS `pre`
	FROM student AS S,(SELECT @pre_con:=NULL,@trank:=0) AS T
	ORDER BY S.continent,S.NAME
) AS A

按照排名放置数据

用上面的排名算法，得到的排名数据，格式为：name,continent,trank。

现要明确，

第1行数据，必须来自排名为1的所有行；

……

第i行数据， 必须来自排名为i的所有行；

这需要一个聚合操作,因此对排名数据,用group by分组.

每组内部,要根据continent确定name属于一个属性A.那么,此行属性A的值为name,其它属性值为NULL.

逻辑为:

MAX(if(A.continent = 'America',A.NAME,NULL)) AS `America`,
MAX(if(A.continent = 'Asia',A.NAME,NULL)) AS `Asia`,
MAX(if(A.continent = 'Europe',A.NAME,NULL)) AS `Europe`

结合两种排名算法,最终结果为:

SELECT 
MAX(if(A.continent = 'America',A.NAME,NULL)) AS `America`,
MAX(if(A.continent = 'Asia',A.NAME,NULL)) AS `Asia`,
MAX(if(A.continent = 'Europe',A.NAME,NULL)) AS `Europe`
FROM
(
	SELECT S1.continent,S1.NAME,S1.row_id,COUNT(*) AS `trank`
	FROM 
	(
		SELECT S.*,@row_id:=(@row_id + 1) AS `row_id`
		FROM student AS S,(SELECT @row_id:=0) AS T
	) AS S1 
	JOIN 
	(
		SELECT S.*,@n_row_id:=(@n_row_id + 1) AS `n_row_id`
		FROM student AS S,(SELECT @n_row_id:=0) AS T
	) AS S2 
		ON (S1.continent = S2.continent AND (S1.NAME > S2.NAME OR (S1.NAME = S2.NAME AND S1.row_id >= S2.n_row_id)))
	group BY S1.continent,S1.NAME,S1.row_id
	order BY S1.continent,S1.NAME
) AS A
GROUP BY A.trank

或者是:

SELECT 
MAX(if(A.continent = 'America',A.NAME,NULL)) AS `America`,
MAX(if(A.continent = 'Asia',A.NAME,NULL)) AS `Asia`,
MAX(if(A.continent = 'Europe',A.NAME,NULL)) AS `Europe`
FROM
(
	SELECT S.*,
	@trank:=if(@pre_con = S.continent,
		@trank + 1,
		1
	) AS `trank`,
	@pre_con:=S.continent AS `pre`
	FROM student AS S,(SELECT @pre_con:=NULL,@trank:=0) AS T
	ORDER BY S.continent,S.NAME
) AS A
GROUP BY A.trank

知识点：