sql面试经典题型

一、给3张表，部门，员工，成绩，统计每个部门大于80分的员工数量，并降序排序。

1、只统计满足要求的部门-》需要内连接 inner join on

2、筛选大于80分的-》where条件判断

3、统计满足要求的部门的员工数量-》使用分组计算-》group by 部门id having聚合函数

4、降序排序-》order by 员工数量 decs

表结构：
部门表 (department): 部门ID (dept_id) 和部门名称 (dept_name)。
员工表 (employee): 员工ID (emp_id)、员工姓名 (emp_name) 和所属部门ID (dept_id)。
成绩表 (score): 员工ID (emp_id) 和成绩 (score)。

select d.dept_id, count(distinct e.emp_id) as 员工数量
from department d 
inner join employee e on e.dept_id=d.dept_id
inner join score s on s.emp_id=e.emp_id
where s.score>80
group by d.dept_id
order by 员工数量 desc;

优化sql查询，提交检索效率，可以通过：

1、先找到所有成绩score>80的再连接表格统计

2、case when then end内联条件判断代替join过滤，减少一次表连接操作

3、使用coalesce确保空值转为0

select d.dept_id, 
coalesce(count(distinct case when s.score>80 then e.emp_id end)) as 员工数量
from department d 
inner join employee e on e.dept_id=d.dept_id
inner join (
select emp_id from score where score>80 GROUP BY emp_id---过滤去重重复员工id
) s on s.emp_id=e.emp_id
group by d.dept_id
order by 员工数量 desc;

备注1：要求查每个部门大于80分的员工数量大于3个的

group by d.dept_id having count(distinct e.emp_id)>3

备注2：要求查每个部门每个员工都大于80分的员工数量

group by e.dept_id,e.emp_id having min(s.score>80)

说明：having与 where的区别是 where 字句在聚合前先筛选记录，作用在 group by 和 having 字句前，而 having 子句在聚合后对组记录进行筛选。

二、查询出每门课都大于80分的学生姓名。

1、方法一，使用not in，排查存在分数小于等于80分的学生姓名

2、方法二，使用not exists子查询，筛选存在分数小于等于80分的学生姓名，同not in

3、方法三，使用左连接，右边表筛选存在分数小于等于80分的学生姓名，同not in

4、方法四，逆向思维，最低分也大于80分，min()

5、方法五，自表关联，学生总科目数=大于80分的科目数

方法一：
select distinct Name from TestScores where Name not in
(select Name from TestScores where Score <= 80);

方法二：
select distinct Name from TestScores as t1
where not exists
(select t2.Name from TestScores as t2 where t2.Score<=80
and t1.Name=t2.Name);

方法三：
select distinct t1.Name from TestScores as t1
left join (select Name from TestScores where Score <=80) as t2
on t1.Name = t2.Name
where t2.Name is null;   -----左连接去空

方法四：
select Name,min(Score)
from TestScores
group by Name
having min(Score)>80;

方法五：
select t1.Name from
(select Name,count(1) as toatalNum from TestScores group by Name) as t1, (select Name,count(1) as above80Num from TestScores where Score>=80 group by Name) as t2
where t1.Name=t2.Name and t1.toatalNum=t2.above80Num;

三、如何从“member”表中手机号“phonenumber”重复且非空的用户。

1、in子查询，判断该条数据的手机号是否存在重复的非空手机号

2、子查询中，手机号不为空，手机号分组统计数量大于1

select * from member 
where phonenumber in(
select phonenumber from member
where phonenumber not in null
group by phonenumber 
having count(1)>1)

执行顺序：使用 phonenumber is not null 排除空数据，再通过分组统计过滤出出现次数大于 1 次的 phonenumber 列表，利用 in 子查询获得这些手机号重复额用户信息。