|
马上注册,结交更多好友,享用更多功能^_^
您需要 登录 才可以下载或查看,没有账号?立即注册
x
题目描述:
- 一所美国大学有来自亚洲、欧洲和美洲的学生,他们的地理信息存放在如下 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
- 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
复制代码
- SELECT
- America, Asia, Europe
- FROM
- (SELECT @as:=0, @am:=0, @eu:=0) t,
- (SELECT
- @as:=@as + 1 AS asid, name AS Asia
- FROM
- student
- WHERE
- continent = 'Asia'
- ORDER BY Asia) AS t1
- RIGHT JOIN
- (SELECT
- @am:=@am + 1 AS amid, name AS America
- FROM
- student
- WHERE
- continent = 'America'
- ORDER BY America) AS t2 ON asid = amid
- LEFT JOIN
- (SELECT
- @eu:=@eu + 1 AS euid, name AS Europe
- FROM
- student
- WHERE
- continent = 'Europe'
- ORDER BY Europe) AS t3 ON amid = euid
复制代码
注意事项:
1.参考连接:https://blog.csdn.net/github_26672553/article/details/53995625 |
|