一、分组比较
1、查找每门课程中成绩最好的学生的姓名和该学生的课程及成绩。
学生表（学号，姓名，出生日期，性别）
成绩表（学号，课程号，成绩）
课程表（课程号，课程名称，教师号）
select a.姓名, c.课程名称, b.成绩
from 学生表 a left join 成绩表 b on a.学号=b.学号
left join 课程表 on b.课程号=c.课程号
where (b.课程号, b.成绩) in
(select 课程号, max(成绩) from 成绩表 group by 课程号);
【举一反三】找出每个部门工资最高的员工
employee表（id , name, salary, departmentid）
department表（id, name）
select a.name, a.salary, b.name
from employee a left join department b
on a.departmentid= b.id
where (a.name, a.salary) in
(select name, max(salary) from employee group by departmentid);
2、查找单科成绩高于该科目平均成绩的学生名单
各科成绩表（姓名，科目，成绩）
select * from
(select 姓名, 科目, 成绩, avg(成绩) over (partition by 科目) as avg_score
from 各科成绩表) as a
where 成绩>avg_score;
【举一反三】使用关联子查询查找单科成绩高于该科目平均成绩的学生名单
select * from 各科成绩表 as a
where 成绩 > (select avg(成绩) from 各科成绩表 as b where a.科目=b.科目);
二、top N问题
首先，对专用窗口函数rank，dense_rank，row_number进行区分：
select *, rank() over(order by 成绩 desc) as ranking,
dense_rank() over(order by 成绩 desc) as dense_rank,
row_number() over(order by 成绩 desc) as row_num
from 班级;
结果为：
从中可知：
（1）rank函数：1， 1， 1， 4
（2）dense_rank函数：1， 1， 1， 2
（3）row_number函数：1， 2， 3， 4
1、查询每门课程的前3高成绩
注：如果出现并列第一的情况，则同为第一名
成绩表（课程号，学号，成绩）
select 课程号, 学号, 成绩, 排名 from
(select 课程号, 学号, 成绩, dense_rank() over(partition by 课程号 order by 成绩 desc) as 排名
from 成绩表) as a
where 排名<=3;
【举一反三】查找每个部门前三高工资的员工
employee表（id, name, salary, departmentid）
select departmentid, name from
(select * , dense_rank() over( paitition by departmentid order by salary desc) as ranking
from employee) a
where ranking <=3;
2、经典top N问题SQL模板：
select * from
(select * , row_number() over(partition by 要分组的列名 order by 要排序的列名 desc) as 排名
from 表名) as a
where 排名 <=N;
三、用户满意度问题
1、分析学校人员对课程的满意度
注：满意度=（教师和学生对课程都满意且已在当前系统中的用户）/（学校里的人数）
满意度表（教师编号，学生编号，是否满意）
用户表（编号，是否在系统，角色）
select sum( case when 满意度表.是否满意='是' then 1 else 0 end)/count(满意度表.是否满意) as 满意度
from 满意度表
left join 用户表
on (满意度表.教师编号=用户表.编号 or 满意度表.学生编号=用户表.编号)
where 用户表.是否在系统 = '是';
【举一反三】查询非禁止用户的取消率
users表（user_id, banned, role）
trips表（id, client_id, driver_id, city_id, status, request_at）
注：其中status表示行程类型，completed表行程正常结束，cancelled_by_driver表因司机原因取消，cancelled_by_client表因乘客原因取消。
select sum(if(trips.status = 'completed', 0,1))/count(trips.status) as 取消率
from trips
left join users
on (trips.driver_id=users.user_id or trips.client_id=users.user_id)
where users.banned='no'
group by trips.request_at;
四、连续出现N次的内容
利用“自连接”的思路：“自连接”的本质是把一张表复制出多张一模一样的表来使用。
select 列名 from 表名 as 别名1, 表名 as 别名2;
以上SQL语句则可实现表的复制。
1、查找出至少连续出现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.成绩；
【举一反三】至少连续3天未出勤的员工
员工表（name, date）
select distinct a.name as 最终答案
from employee as a, employee as b, employee as c
where a.date=b.date-1 and b.date=c.date-1 and a.name=b.name and b.name=c.name；
五、交换数据
1、交换相邻两个学生的座位（偶数位）
学生表（座位号，姓名）
select (case when mod(座位号,2)=1 then 座位号+1 else 座位号-1 end) as 序号, 姓名
from 学生表 order by 序号;
【举一反三】更换相邻位置学生的座次（奇数位）
座位表（id, student）
select (case when mod(id,2) != 0 and id!=counts then id+1
when mod(id,2) != 0 and id =counts then id
else id-1
end) as new_id, student
from seat, (select count(*) as counts from seat) as b;
将最后一个座位号作为条件判断使用时，可使用子查询，以便调用。
六、按条件修改数据
1、交换班级，1、2班交换，0变为3班，要求只使用update，不使用select语句
班级表（学号，班级）
update class
set 班级 = (case 班级 when 1 then 2
when 2 then 1
else 3
end);
【举一反三】交换所有表中的性别，要求只使用update语句（性别用f和m表示）
salary表（id, name, sex, salary）
update salary
set sex = (case sex when 'f' then 'm'
else 'f'
end);
七、比较日期数据
1、找出所有比前一天营业额更高的数据
datediff函数返回相差的天数，前-后
timestampdiff函数返回类型可自定义，后-前
日销表（ID，日期，营业额）
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 timestampdiff(day, a.日期, b.日期)=-1
where a.营业额 > b.营业额；
【举一反三】找出比前一天温度更高的id和日期
weather表（ID，date，temp）
select a.id, a.date
from weather as a cross join weather as b
on datediff(a.date, b.date)=1
where a.temp>b.temp;
或者
select a.id, a.date
from weather as a cross join weather as b
on timestampdiff(day, a.date, b.date)=-1
where a.temp>b.temp;
八、查找不在表里的数据
1、查找不是近视眼的学生名单
学生表（学号，姓名）
近视学生表（序号，学生学号）
select a.学号, a.姓名
from 学生表 as a left join 近视学生表 as b
on a.学号=b.学生学号
where b.序号 is null;
【举一反三】查找所有从不订购任何产品的客户
顾客姓名表customers（id，name）
购买记录表orders（id，customerid）
select a.name
from customers as a left join orders as b
on a.id=b.customerid
where b.customerid is null;
或者
select a.name from customers as a where a.id not in
(select b.customerid from orders as b );
九、多表查询
1、查找所有学生的学号、姓名、课程和成绩
成绩表（学号，课程，成绩）
信息表（学号，姓名）
select b.学号, b.姓名, a.课程, a.成绩
from 成绩表 as a left join 信息表 as b
on a.学号=b.学号;
【举一反三】无论是否有地址信息，都要查询出fistname，lastname，city，state
person表（personid，firstname，lastname）
address表（addressid，personid，city，state）
select a.firstname, a.lastname, b.city, b.state
from person as a left join address as b
on a.personid = b.personid;
十、查找第N高的数据
1、查找语文课成绩第二高的学生成绩
成绩表（学号，课程，成绩）
-- 方法1
select max(distinct 成绩)
from 成绩表
where 课程='语文' and
成绩 < (select max(distinct 成绩) from 成绩表 where 课程='语文');
-- 方法2
select distinct 成绩
from 成绩表
where 课程='语文'
order by 课程，成绩 desc
limit 1 offset 1;
-- 考虑特殊情况，如果没有第二高的成绩，返回空值
select ifnull(
(select max(distinct 成绩) from 成绩表
where 课程='语文'
and 成绩<(select max(成绩) from 成绩表 where 课程='语文')
)
, null) as '语文第二名成绩';
【举一反三】查找employee中第二高的薪水，如果不存在，返回null
employee表（id，salary）
select ifnull(
(select distinct salary from employee order by salary desc
limit 1 offset 1), null
) as secondhihgestsalary;
十一、提高SQL查询效率
1、select子句中尽量避免使用*
原因：会导致数据库引擎扫描全表，增加运行时间
解决方法：需要哪些列的数据，就提取哪些列的数据
2、where子句比较符号左侧避免出现函数、表达式
原因：会导致数据库引擎扫描全表，增加运行时间
解决方法：遇到where子句中有函数或加减乘除运算的，应将其移到比较符号的右侧
例如：查询每人加5分后，成绩在90分以上的同学的学号
对where 成绩+5>90的优化方法为：where 成绩>90-5
3、尽量避免使用in和not in
原因：会导致数据库引擎扫描全表，增加运行时间
解决方法：可以使用between代替
例如：查看8、9两个同学的成绩
对select 学号,成绩 from 成绩表 where 学号 in (8,9)的优化方法为：
select 学号,成绩 from 成绩表 where 学号 between 8 and 9;
4、尽量避免使用or
原因：会导致数据库引擎扫描全表，增加运行时间
解决方法：可以使用union代替
例如：查看成绩是88分或者89分学生的学号
对select 学号 from 成绩表 where 成绩 = 88 or 成绩 = 89的优化为：
select 学号 from 成绩表 where 成绩 = 88
union
select 学号 from 成绩表 where 成绩 = 89;
虽然语句变长，但是处理大量数据时，可省下很多时间，非常值得
5、使用limit子句限制返回的数据行数
原因：返回全部结果，会增加运行时间
十二、查找重复数据
1、查找学生表中所有重复的学生名
学生表（学号，姓名）
-- 方法一
select 姓名 from
(select 姓名, count(姓名) as 计数
from 学生表
group by 姓名
) as 辅助表
where 计数 > 1;
-- 方法二
select 姓名
from 学生表
group by 姓名
having count(姓名) > 1;
【举一反三】查找表中重复出现n次的数据
select 列名
from 表名
group by 列名
having count(列名) > n;
