一所美国大学有来自亚洲, 欧洲和美洲的学生, 他们的地理信息存放在如下 student 表中.
- | name | continent |
- |--------|-----------|
- | Jack | America |
- | Pascal | Europe |
- | Xi | Asia |
- | Jane | America |
写一个查询语句实现对大洲 (continent) 列的 透视表 操作, 使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面. 输出的标题应依次为美洲 (America), 亚洲(Asia) 和欧洲(Europe). 数据保证来自美洲的学生不少于来自亚洲或者欧洲的学生.
对于样例输入, 它的对应输出是:
- | America | Asia | Europe |
- |---------|------|--------|
- | Jack | Xi | Pascal |
- | Jane | | |
进阶: 如果不能确定哪个大洲的学生数最多, 你可以写出一个查询去生成上述学生报告吗?
leetcode 数据库题目全部题解
解法一
按 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
作者: jason-2
链接: https://leetcode-cn.com/problems/students-report-by-geography/solution/ji-yu-pai-ming-suan-fa-by-jason-2/
来源: http://www.bubuko.com/infodetail-3296919.html