MYSQL练习题
学习网址：
https://houzidata.gitbook.io/sql/
下面是自己的学习记录
一、简单查询
如何查找重复数据？
题目1
编写一个 SQL 查询，查找学生表中所有重复的学生名。
（1）方法1：使用辅助表
【答案】
SELECT 姓名FROM
(SELECT 姓名,COUNT(姓名) AS 计数FROM 学生表GROUP BY 姓名) AS 辅助表WHERE 计数 > 1;
知识点1
① group by子句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理
② count函数：
count（*）：统计表一共有多少行（空行也算）
count(列名)：统计某列中的有效数据多少个（不算空值）
（2）方法2：使用HAVING子句
【答案】
SELECT 姓名FROM 学生表GROUP BY 姓名HAVING COUNT(姓名) > 1;
知识点2
③ 查询语句各种子句的执行顺序：
from->where->group by->select->order by->limit
④ 举一反三：查找表中出现N次以上的数据
SELECT 列名FROM 表名GROUP BY 列名HAVING COUNT(列名) > N;
题目2
编写一个 SQL 查询，查找 Person表中所有重复的电子邮箱。
【答案】
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) > 1;
二、复杂查询
如何查找第 N 高的数据？
题目1
现在有 “成绩表”，记录了学生选修课程的名称以及成绩。现在需要找出语文课中成绩第二高的学生成绩。如果不存在第二高成绩的学生，那么查询应返回 null。
|
成绩表 |
||
|
学号 |
课程 |
成绩 |
|
1 |
语文 |
90 |
|
1 |
数学 |
65 |
|
2 |
语文 |
68 |
|
2 |
数学 |
96 |
|
3 |
数学 |
55 |
（1）方法1：先选出所有选修了“语文”课的学生成绩，然后找出语文成绩查询最大的成绩记为 a，最后再找出小于 a 的最大值就是课程成绩的第二高值。考虑到可能不存在第二高成绩的学生，所以还需要使用到ifnull函数。
【答案】
select ifnull(
(select max(distinct 成绩) from 成绩表where 成绩<(select max(成绩) from 成绩表 where 课程='语文') and 课程='语文')
,null) as '语文课第二名成绩';
知识点1
① 如果我们需要去除重复的记录，可以使用DISTINCT关键字来实现。
语法： SELECT DISTINCT 字段 FROM ……;
例子：SELECT DISTINCT job FROM t_emp;
1) 使用DISITINCT的SELECT子句中只能查询一列数据，如果查询多列，去除重复记录就会失效。
SELECT DISTINCT job,ename FROM t_emp; # 错误
2) DISTINCT关键字只能在SELECT子句中使用一次，而且必须写在第一个字段的前面。
SELECT DISTINCT job, DISTINCT ename FROM t_emp; # 错误
SELECT ename,DISTINCT job FROM t_emp; # 错误
② ifnull(a,b) 函数解释：
如果 value1 不是空，结果返回 a
如果 value1 是空，结果返回 b
（2）方法2：使用 limit 和 offset
【答案】
select ifnull(
(select distinct 成绩 from 成绩表 where 课程='语文' order by 成绩 desc
limit 1,1),null) as '语文课第二名成绩';
知识点2
① limit子句：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
1) LIMIT 接受一个或两个数字参数，参数必须是一个整数常量。offset参数指定第一个返回记录行的偏移量，rows指定返回记录行的最大数目。offset默认值为0。
2) 图解：
|
示例 |
解释 |
|
LIMIT 5,10 |
检索记录行6-15共10行 |
|
LIMIT 5 = LIMIT 0,5 |
检索前5个记录行，LIMIT n 等价于 LIMIT 0,n |
|
LIMIT 2 OFFSET 3 = LIMIT 3,2 |
检索4-5两行 |
② ORDER BY 列名[ASC|DESC]：ASC 代表升序(默认)，DESC代表降序,默认使用升序
题目2
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
【答案】
方法1:
SELECT IFNULL(
(SELECT max(DISTINCT Salary)
FROM Employee
WHERE Salary < (SELECT max(Salary) FROM Employee)),NULL) AS SecondHighestSalary ;
方法2：
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC LIMIT 1,1),null) AS SecondHighestSalary;
题目3
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
答案：
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN
SET n = N-1;
RETURN (
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT n, 1
);END
注意：LIMIT的参数只能是一个确切的数，不能是表达式
三、多表查询
多表如何查询？
题目1
现在有两个表：
“学生表” 记录了学生的基本信息，有 “学号”、“姓名”。
“成绩” 表记录了学生选修的课程，以及对应课程的成绩。这两个表通过 “学号” 进行关联。现在要查找出所有学生的学号，姓名，课程和成绩。
|
成绩表 |
|
学生信息表 |
|||
|
学号 |
课程 |
成绩 |
学号 |
姓名 |
|
|
1 |
语文 |
90 |
1 |
张三 |
|
|
1 |
数学 |
65 |
2 |
李四 |
|
|
2 |
语文 |
68 |
3 |
王五 |
|
|
2 |
数学 |
96 |
4 |
赵六 |
|
|
3 |
数学 |
55 |
|
|
|
|
运行结果 |
|||
|
学号 |
姓名 |
课程 |
成绩 |
|
1 |
张三 |
语文 |
90 |
|
1 |
张三 |
数学 |
65 |
|
2 |
李四 |
语文 |
68 |
|
2 |
李四 |
数学 |
96 |
|
3 |
王五 |
数学 |
55 |
|
4 |
赵六 |
null |
null |
【答案】
需要使用到多表联结，因为需要保留所有学生的信息，所以学生信息表左联结成绩表
select a.学号,a.姓名,b.课程,b.成绩
from 学生 as a left join 成绩 as b
on a.学号=b.学号;
知识点1
多表联结图片解析
题目2
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State
【答案】
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId;
如何查找不在表中的数据？
题目1
下面是学生的名单，表名为 “学生表”；近视学生的名单，表名为 “近视学生表”。请问不是近视眼的学生都有谁？（“学生表”表中的学号与 “近视学生” 表中的学生学号一一对应）
|
学生表 |
|
近视学生表 |
||
|
学号 |
姓名 |
序号 |
学生学号 |
|
|
0001 |
周周 |
1 |
0001 |
|
|
0002 |
丽丽 |
2 |
0002 |
|
|
0003 |
王王 |
3 |
0003 |
|
|
0004 |
张张 |
|
||
|
0005 |
莉莉 |
|||
【答案】
不是近视眼的学生等于在学生表中，但是不在近视学生表中
select a.姓名 as 不近视的学生名单
from 学生表 as a left join 近视学生表 as b
on a.学号=b.学生学号
where b.序号 is null;
题目2
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
【答案】
SELECT a.Name AS Customers
FROM Customers AS a
LEFT JOIN Orders AS b
ON a.Id = b.CustomerId
WHERE b.CustomerId is null;
你有多久没涨过工资了？
题目1
“雇员表 “中记录了员工的信息，“薪水表“中记录了对应员工发放的薪水。两表通过“雇员编号” 关联。查找当前所有在职雇员入职以来的薪水涨幅，给出雇员编号以及其对应的薪水涨幅，并按照薪水涨幅进行升序。
（注：薪水表中结束日期为 2004-01-01 的才是当前员工，否则是已离职员工）
【解题思路】
要求出当前所有雇员入职以来的薪水涨幅，薪水涨幅 = 当前薪水 - 入职薪水。
当前薪水是 “薪水表 “中的 “结束日期”=2004-01-01，这一行对应的薪水。
入职薪水是 “雇员表 “中的 “雇用日期”=“薪水表 “中的 “起始日期”，这一行对应的薪水。
1、求当前薪水
select 雇员编号,薪水 as 当前薪水from 薪水表where 结束日期 = '2004-01-01';
2、求入职薪水：使用左联结，保留雇员表的所有信息
select a.雇员编号,薪水 as 入职薪水
from 雇员表 as a left join 薪水表 as b
on a.雇员编号 = b.雇员编号where a.雇用日期 = b.起始日期;
3、筛选在职雇员
where 雇员编号 in (select 雇员编号from 薪水表where 结束日期 = '2004-01-01');
4、结合2和3的SQL语句便是在职员工的入职薪水
select a.雇员编号,薪水 as 入职薪水
from 雇员表 as a left join 薪水表 as b
on a.雇员编号 = b.雇员编号
where a.雇用日期 = b.起始日期 and a.雇员编号 in (select 雇员编号from 薪水表where 结束日期 = '2004-01-01');
5、薪水涨幅：将1和4的两张表使用左联结得到薪水涨幅表
select m.雇员编号,当前薪水-入职薪水 as 薪水涨幅
from m left join n
on m.雇员编号 = n.雇员编号;
6、按薪水涨幅进行升序：使用 order by 子句对查询结果排序。
7、最终SQL语句
select m.雇员编号,当前薪水-入职薪水 as 薪水涨幅
from
/*当前薪水表*/(select 雇员编号,薪水 as 当前薪水 from 薪水表 where 结束日期 = '2004-01-01') as m /*当前薪水表*/
left join
/*入职薪水表*/(select a.雇员编号,薪水 as 入职薪水 from 雇员表 as a left join 薪水表 as b on a.雇员编号 = b.雇员编号where a.雇用日期 = b.起始日期 and a.雇员编号 in
(/*在职员工*/select 雇员编号from 薪水表where 结束日期 = '2004-01-01'/*在职员工*/)) as n/*入职薪水表*/
on m.雇员编号 = n.雇员编号
order by 薪水涨幅;
题目2
查找所有学生开学以来的成绩涨幅，给出学生编号以及其对应的成绩涨幅，并按照成绩涨幅进行升序。
【答案】
select m.学生编号,当前成绩-入学成绩 as 成绩涨幅
from
(select 学生编号,成绩 as 当前成绩 from 成绩表 where 结束日期 = '2011-10-02') as m
left join
(select a.学生编号,b.成绩 as 入学成绩 from 学生表 as a left join 成绩表 as b on a.学生编号 = b.学生编号 where a.入学日期 = b.起始日期) as n
on m.学生编号 = n.学生编号order by 成绩涨幅;
如何比较日期数据？
题目1
下面是某公司每天的营业额，表名为 “日销”。“日期” 这一列的数据类型是日期类型（date）。请找出所有比前一天（昨天）营业额更高的数据。（前一天的意思，如果 “当天” 是 1 月 2 号，“昨天”（前一天）就是 1 号）
【答案】
select a.ID, a.日期, a.销售额（万元）
from 日销 as a cross join 日销 as b
on datediff(a.日期, b.日期) = 1
where a.销售额（万元） > b.销售额（万元）;
或
select a.ID, a.日期, a.销售额（万元）
from 日销 as a cross join 日销 as b
on datediff(a.日期, b.日期) = -1
where a.销售额（万元） > b.销售额（万元）;
知识点1
① 交叉联结（corss join）
交叉联结的概念：不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的 行数等于两个表行数的乘积 （例如：T_student和T_class，返回4*4=16条记录），如果带where，返回或显示的是匹配的行数。
1）不带where：
select * from T_student cross join T_class等于select * from T_student, T_class
2）有where子句，往往会先生成两个表行数乘积的数据表，然后才根据where条件从中选择。
select * from T_student s cross join T_class c where s.classId = c.classId
(注:cross join后加条件只能用where,不能用on)
② 时间计算函数
1）函数1：datediff(日期 1, 日期 2)：
得到的结果是日期 1 与日期 2 相差的天数。
如果日期 1 比日期 2 大，结果为正；如果日期 1 比日期 2 小，结果为负。
2）函数2：timestampdiff(时间类型, 日期 1, 日期 2)
这个函数和上面 datediff 的正、负号规则刚好相反。
日期 1 大于日期 2，结果为负，日期 1 小于日期 2，结果为正。
题目2
编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。返回结果 不要求顺序 。
【答案】
select a.id
from Weather as a cross join Weather as b
on datediff(a.recordDate,b.recordDate) = 1
where a.Temperature > b.Temperature;
如何交换数据？
题目1
小明是一所学校的老师，她有一张 “学生表”，平时用来存放座位号和学生的信息。其中，座位号是连续递增的。总的座位数是偶数。
现在，小明想改变相邻俩学生的座位。你能不能帮她写一个 sql 查来输出想要的结果呢？示例查询结果如下：
【答案】
select
(case
when mod(座位号, 2) != 0 then 座位号 + 1
when mod(座位号, 2) = 0 then 座位号 - 1
end) as '交换后座位号',
姓名from 座位表order by 交换后座位号;
知识点1
SQL求余函数：mod(n,m) ，返回 n 除以 m 的余数。
知识点2
CASE语法：
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END
或CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END
题目2
小美是一所中学的信息科技老师，她有一张seat座位表，平时用来储存学生名字和与他们相对应的座位id。其中纵列的id是连续递增的，小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
【答案】
SELECT
(CASE
# 当座位号是奇数并且不是最后一个座位号时,座位号加1
WHEN MOD(id, 2) != 0 AND id!= counts THEN id + 1
# 当座位号是奇数并且是最后一个座位号时,座位号不变
WHEN MOD(id, 2) != 0 AND id = counts THEN id
# 当座位号是偶数时，座位号减1
ELSE id - 1
END) AS id,
student
FROM seat,
# 最后一个座位号，记为counts
(SELECT COUNT(*) AS counts FROM seat) AS seat_counts
ORDER BY id ASC;
如何找出最小的N个数？
题目1
“学生表” 里记录了学生的学号、入学时间等信息。“成绩表” 里是学生选课成绩的信息。两个表中的学号一一对应。（滴滴 2020 年面试题）
现在需要：
（1）筛选出 2017 年入学的 “计算机” 专业年龄最小的 3 位同学名单（姓名、年龄）
（2）统计每个班同学各科成绩平均分大于 80 分的人数和人数占比
【题目1答案】
select 姓名,年龄
from 学生表
where 专业='计算机' and year(入学时间)=2017
order by 年龄 asc
limit 3;
【题目2答案】
（1）先算出每个同学的平均成绩：
select 学号,avg(分数) as 平均成绩from 成绩表group by 学号;
（2）使用 sum 函数和 case 表达式来统计平均成绩大于 80 的人数：
select sum(case
when 平均成绩>80 then 1 else 0
end) as 人数from 临时表；
图解过程：
（3）算出平均成绩大于80分的人数占比
select sum(case when 平均成绩>80 then 1
else 0 end)/count(学号) as 人数占比from 临时表；
（4）输出结果
select a.班级,sum(case when b.平均成绩>80 then 1 else 0 end) as 人数,sum(case when b.平均成绩>80 then 1 else 0 end)/count(a.学号) as 人数占比
from 学生表 as a left join (select 学号,avg(分数) as 平均成绩from 成绩表group by 学号) as b
on a.学号=b.学号
group by 班级;
知识点1
遇到有筛选条件的统计数量问题时，使用 case 表达式筛选出符合条件的行为 1，否则为 0。然后用汇总函数（sum）对 case 表达式输出列求和。
有筛选条件的统计数量问题的万能模板：
select sum(case when <判断表达式> then 1
else 0 end
) as 数量 from 信息表；
题目2
某网站有购买记录表，找出消费最大的 2 名顾客，输出顾客ID 和消费金额。
【答案】
select 顾客ID,消费金额
from 购买记录表
order by 消费金额 desc
limit 2;
题目3
某网站有顾客表和消费表，请统计每个城市的顾客平均消费在 1000 元以上的人数，输出城市，人数
【答案】
先得到平均消费表作为临时表，然后使用sum+case求出平均消费大于1000的人数，最后顾客表和临时表联结得到结果
select a.城市,sum(case when b.平均消费>1000 then 1 else 0 end) as 人数
from 顾客表 as a
left join(select ID,avg(消费金额) as 平均消费from 消费表group by ID) as b
on a.ID=b.ID
group by 城市;
行列互换问题，怎么办？
题目1
下面是表名为 cook 的表
要求查询结果如下：
【答案】
（1）输出行列互换的表结构
select 年,m1,m2,m3,m4from cook;
（2）使用case语句匹配年份和月份，相匹配则为对应值，反之则为0
（3）去掉0 并简化行数
select 年,
max(case 月 when '1' then 值 else 0 end) as 'm1',
max(case 月 when '2' then 值 else 0 end) as 'm2',
max(case 月 when '3' then 值 else 0 end) as 'm3',
max(case 月 when '4' then 值 else 0 end) as 'm4'
from cook
group by 年;
知识点1
遇到行列互换的问题，可以用下面的万能模版来解决。
select A,
-- 第2步，在行列互换结果表中，其他列里的值分别使用case和max来获取
max(case B when 'm' then C else 0 end) as 'm',
max(case B when 'n' then C else 0 end) as 'n'
from cook
-- 第1步，在行列互换结果表中按第1列分组
group by A;
题目2
下面是学生的成绩表 (表名：成绩表，列名：学号，课程，成绩)
使用 sql 语句实现将该表行转化为下面的表结构：
【答案】
select 学号,
-- 第2步，在行列互换结果表中，其他列里的值分别使用case和max来获取
max(case 课程 when '语文' then 成绩 else 0 end) as 语文成绩,
max(case 课程 when '数学' then 成绩 else 0 end) as 数学成绩
from 成绩表
-- 第1步，在行列互换结果表中按第1列分组
group by 学号;
找出连续出现 N 次的内容？
题目1
下面是学生的成绩表（表名 score，列名：学号、成绩），使用 SQL 查找所有至少连续出现 3 次的成绩。例如，“成绩” 这一列里 84 是连续出现 3 次的成绩。
【答案】
“连续”、“成绩相等”、采用自连接（自连接（自身连接）的本质是把一张表复制出多张一模一样的表来使用）。
select distinct a.成绩 as 最终答案
from score as a,
score as b,
score as c;
where a.学号 = b.学号 - 1
and b.学号 = c.学号 - 1
and a.成绩 = b.成绩
and b.成绩 = c.成绩;
如何分析留存率？
题目1
手机中的相机是深受大家喜爱的应用之一，下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。
用户 id：用户唯一标识；
应用名称：是手机中的某个应用，例如相机、微信、大众点评等。
启动时长：某一天中使用某应用多长时间（分钟）。
启动次数：某一天中启动了某应用多少次。
登陆时间：使用手机的日期。例如 2018-05-01。
现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据：
某日活跃用户（用户 id）在后续的一周内的留存情况（计算次日留存用户数，3 日留存用户数，7 日留存用户数）
指标定义：
某日活跃用户数，某日活跃的去重用户数。
N 日活跃用户数，某日活跃的用户数在之后的第 N 日活跃用户数。
N 日活跃留存率，N 日留存用户数 / 某日活跃用户数
例：登陆时间（20180501 日）去重用户数 10000，这批用户在 20180503 日仍有 7000 人活跃，则 3 日活跃留存率为 7000/10000=70%
所需获得的结果格式如下：
【答案】
select a.登陆时间,
count(distinct a.用户id) as 活跃用户数,
count(distinct when 时间间隔=1 then 用户id else null end) as 次日留存数,
count(distinct when 时间间隔=1 then 用户id else null end) as 次日留存数 / count(distinct a.用户id) as 次日留存率,
count(distinct when 时间间隔=3 then 用户id else null end) as 三日留存数,
count(distinct when 时间间隔=3 then 用户id else null end) as 三日留存数 / count(distinct a.用户id) as 三日留存率,
count(distinct when 时间间隔=7 then 用户id else null end) as 七日留存数,
count(distinct when 时间间隔=7 then 用户id else null end) as 七日留存数 / count(distinct a.用户id) as 七日留存率
from
(
select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔
from
(select a.用户id,a.登陆时间,b.登陆时间
from 用户行为信息表 as a left join 用户行为信息表 as b
on a.用户id = b.用户id
where a.应用名称= '相机') as c
) as d
group by a.登陆时间;
题目2
链家 2018 春招笔试面试：现有订单表和用户表，格式字段如下图：
订单表
|
时间 |
订单 id |
商品 id |
用户 id |
订单金额 |
|
|
|
|
|
|
用户表
|
用户 id |
姓名 |
性别 |
年龄 |
|
|
|
|
|
1.查询 2019 年 Q1 季度，不同性别，不同年龄的成交用户数，成交量及成交金额
2.2019 年 1-4 月产生订单的用户，以及在次月的留存用户数
【问题1答案】
select b.性别,b.age,
count(distinct a.用户id) as 用户数,
count(订单id),
sum(a.订单金额)
from 订单表 as a inner join 用户表 as b
on a.用户id = b.用户id
where a.时间 between '2019-01-01' and '2019-03-31'
group by b.性别,b.age;
【问题2答案】
select a.用户id,
count(case
when timestampdiff(month,b.时间,a.时间)=1 then a.用户id else null
end) as 次月留存用户数
from 订单表 as a inner join 订单表 as b
on a.用户id = b.用户id
where a.时间 between '2019-01-01' and '2019-04-30'
group by a.用户id
如何找到前20%的数据？
题目1
用户访问次数表，列名包括用户编号、用户类型、访问量。要求在剔除访问次数前 20% 的用户后，每类用户的平均访问次数。（拼多多、网易面试题）
【答案】
select 用户类型,avg(访问量)from
(select * from
(
# 按照访问量降序排序
select *,
row_number() over(order by 访问量 desc) as 排名from 用户访问次数表) as a
# 筛选排名除去前20%的数据
where 排名 > (select max(排名) from a) * 0.2) as b
group by 用户类型;
知识点1
（1）当有 “每个” 出现的时候，要想到用分组汇总，下图是常用的汇总函数：
（2）选出前百分之 N 的问题如何解决？下面是这类问题的解决模版
1）先使用窗口函数对数据排名得到临时表 a
select *,row_number() over(order by 排名的列 desc) as 排名from 表名;
2）然后用表 a 筛选出前百分之 N 的数据
select * from a where 排名 <= (select max(排名) from a) * 百分之N;
3）如果是剔除前前百分之 N 的数据，也就是选出后 (1 - 百分之 N) 的数据。例如选出后 80% 的数据，就把上面的 where 子句里的 <= 修改成 >
select * from a where 排名 > (select max(排名) from a) * 百分之N;
（3）专用窗口函数row_number, rank(), dense_rank()的区别
- rank()排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果；
- dense_rank()排序相同时会重复，总数会减少，即会出现1、1、2这样的排序结果；
- row_number()排序相同时不会重复，会根据顺序排序。
|
id |
数据 |
rank() |
dense_rank() |
row_number() |
|
1 |
45 |
1 |
1 |
1 |
|
2 |
75 |
2 |
2 |
2 |
|
3 |
75 |
2 |
2 |
3 |
|
4 |
80 |
4 |
3 |
4 |
|
5 |
80 |
4 |
3 |
5 |
|
6 |
80 |
4 |
3 |
6 |
|
7 |
200 |
7 |
4 |
7 |
|
8 |
300 |
8 |
5 |
8 |
如何查找工资前三高的员工？
题目1
“成绩表” 记录了学生的学号，学生选修的课程，以及对应课程的成绩。
为了对学生成绩进行考核，现需要查询每门课程的前 3 高成绩。
注意：如果出现并列第一的情况，则同为第一名。
【答案】
select 课程号,学号,成绩,排名 from
(select *,
dense_rank() over (partition by 课程号
order by 成绩 desc) as 排名from 成绩表) as a where 排名 <=3;
知识点1
经典 topN 问题：每组最大的 N 条记录。这类问题涉及到 “既要分组，又要排序” 的情况，要能想到用窗口函数来实现。
# topN问题 sql模板
select * from (
select *,
row_number() over (partition by 要分组的列名
order by 要排序的列名 desc) as 排名
from 表名) as a where 排名 <= N;
题目2
Employee 表包含所有员工信息，每个员工有其对应的工号 (Id)，姓名 (Name)，工资 (Salary) 和部门编号 ( DepartmentId) 。
查找每个部门前三高工资的员工。例如，根据上述给定的表，查询结果应返回：
【答案】
select DepartmentId,Name,Salary
from (
select *,
dense_rank() over (partition by DepartmentId
order by Salary desc) as ranking
from Employee) as a where ranking <= 3;
如何进行分组比较？
现在有三个表，“学生表”，“课程表”，“成绩表”。
现在要查找出每门课程中成绩最好的学生的姓名和该学生的课程及成绩。需要注意：可能出现并列第一的情况。
【答案】
select c.学号,c.姓名,b.课程,a.成绩
from 成绩表 a left join 课程表 b on a.课程号=b.课程号
left join 学生表 c on a.学号=c.学号
where (a.课程号,a.成绩) in (select 课程号,max(成绩) from 成绩表 group by 课程号);
题目2
有两个表，Employee 表用于记录员工的薪水和在哪个部门，包括员工的 Id、员工的姓名（Name）、薪水（Salary） 和 员工所在部门 Id（Department Id）。
Department 表用于记录公司所有部门的信息，包括部门 Id，部门名称（Name）。
找出每个部门工资最高的员工。例如，根据上述给定的表格，员工（Max）在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
【答案】
select Department.name,Employee.name,Salary
from Employee left join Department on Employee.DepartmentId = Department.Id
where (Employee.DepartmentId,Salary) in
(select DepartmentId,max(Salary) from Employee group by DepartmentId);
双11用户怎么分析？
题目1
现有用户登录时间表，记录每个用户的 id，姓名，邮箱地址和用户最后登录时间。表如下：
问题：生成一张临时表（表名：用户登录表），表中呈现四列数据分别为：姓名，最后登录时间，登录时间排名，登录天数排名
要求：
1.“登录时间排名” 这一列：按时间给出每个人的登录次数，登录时间最早的为 1，之后的分别是 2，3，4 等。
2.“登录天数排名” 这一列：按天给出每个人的登录次数，同一天多次登录认为是同一次，最早标记为 1，之后以此类推。
【答案】
select 姓名,最后登录时间,
row_number() over (partition by 姓名
order by 最后登录时间 asc) as 登录时间排名,
dense_rank() over (partition by 姓名
order by date_format( 最后登录时间,'%Y%m&d' ) asc) as 登录天数排名from 用户登录表 ;
如何分析游戏?
题目1
猴子是一个班级的班主任，由于所带班级的学生成绩普遍不是很好。现在他需要找出每门课程中成绩最差的学生，然后有针对性的辅导。
下面的成绩表，记录来每个学生选修课程的成绩。
请注意：每门课程倒数第一的学生可能有多名，他们的成绩相同。请用你的聪明智慧帮助猴子老师尽快的找出这些学生，帮助他们提升成绩吧。
【答案1】
select b.学号,a.课程号,a.成绩
from
(select 课程号,min(成绩) as 成绩 from 成绩表 group by 课程号) as a inner join
(select * from 成绩表) as b on a.课程号=b.课程号 and a.成绩=b.成绩;
【答案2】
select * from
(select *, min(成绩) over (partition by 课程号) as 最差的成绩from 成绩表) t where 成绩=最差的成绩;
题目2
下面的游戏玩家表（activity 表）记录了游戏玩家在游戏平台上的行为活动。
每行数据记录了该玩家（player）在某天（event_datert 日期)，使用同一台设备（device_id 设备编号，比如苹果手机、pad 是不一样的设备）登录平台后打开的游戏的数目（games_played 游戏数码）。表的主键是 (player, event_date)。
【问题 1】找出每位玩家第一次登陆平台的日期
select player_id as player , min(event_date) as first_login from activity group by player_id;
【问题 2】找出每位玩家首次登陆的设备名称
select a.player_id as player_id, a.device_id
from activity as a left join (select player_id, min(event_date) as first_login from activity group by player_id） as b on a.player_id = b.player_id and a.event_date = b.first_login;
浙公网安备 33010602011771号