一所美国大学有来自亚洲、欧洲和美洲的学生,他们的地理信息存放在如下 student 表中。
| name | continent |
|--------|-----------|
| Jack | America |
| Pascal | Europe |
| Xi | Asia |
| Jane | America |
写一个查询语句实现对大洲(continent)列的 透视表 操作,使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲(America)、亚洲(Asia)和欧洲(Europe)。数据保证来自美洲的学生不少于来自亚洲或者欧洲的学生。
对于样例输入,它的对应输出是:
| America | Asia | Europe |
|---------|------|--------|
| Jack | Xi | Pascal |
| Jane | | |
#建表
DROP TABLE IF EXISTS student;CREATE TABLE student
(NAME VARCHAR(20),
continent VARCHAR(20)
);INSERT INTO student VALUES('Jack', 'America');
INSERT INTO student VALUES('Pascal', 'Europe');
INSERT INTO student VALUES('Xi', 'Asia');
INSERT INTO student VALUES('Jane', 'America');
#答案
SELECT America, Asia, Europe
FROM
(SELECT NAME AS America,row_number() over (ORDER BY NAME ASC) AS rn FROM student WHERE continent='America') a
LEFT JOIN
(SELECT NAME AS Asia,row_number() over (ORDER BY NAME ASC) AS rn FROM student WHERE continent='Asia') b
ON a.rn=b.rn
LEFT JOIN
(SELECT NAME AS Europe,row_number() over (ORDER BY NAME ASC) AS rn FROM student WHERE continent='Europe') c
ON c.rn=a.rn
#展示
#本人错误代码
SELECT
(CASE WHEN s.continent='America' THEN s.name ELSE NULL END) 'America',
(CASE WHEN s.continent='Asia' THEN s.name ELSE NULL END) 'Asia',
(CASE WHEN s.continent='Europe' THEN s.name ELSE NULL END) 'Europe'
FROM student s;
#展示
本题一看,很简单,建表也很简单 但是细细一品真的不容易。
虽然知道使用开窗函数 但是没想到连接 导致这个空值没办法去掉
sql语句还是掌握不够扎实。