sql判断偶数_SQL习题汇总（一）

一、分组比较

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;