13. SQL回顾的优化

# 1.列出自己的掌门比自己年龄小的人
# 优化后，不需要建索引了
select  e.`name`,e.age,e1.`name`,e1.age from emp e
left join dept d on e.deptId = d.id
left join emp e1 on d.CEO=e1.id
where e1.age<e.age;

# 2.列出所有年龄低于自己门派平均年龄的人
# 优化后
select ed.deptName,e.`name`,age,ed.avgAge from emp e
inner join (
	# 各门派的平均年龄
	select deptId,deptName,avg(age) avgAge from emp e1
	left join dept d on d.id = e1.deptId
	where deptId is not null
	group by deptId
) ed on e.deptId = ed.deptId
where e.age < ed.avgAge;

create index idx_deptId on emp(deptId);
create index idx_deptId_age on emp(deptId,age);

# 3.列出至少有2个年龄大于40岁的成员的门派
# 优化后
# 使用straight_join直连
# 它的大体功能与inner join一致，但是可以手动指定前面的是驱动表，后面的是被驱动表
# 使用前必须要了解作用，并且明确两个表的数量级，哪个多，哪个少
select deptName, count(*) num from dept d
straight_join emp e on e.deptId = d.id
where age>40
group by d.deptName,d.id
having num >= 2;

create index idx_deptName on dept(deptName);
create index idx_deptId_age on emp(deptId,age);

# 4.至少有2位非掌门人成员的门派
# 优化后
select  d1.`id`, d1.`deptName`,count(*) from dept d1
straight_join emp e  on e.deptId = d1.id
left join dept d on e.id = d.CEO
where d.id is null
group by d1.`deptName`,d1.`id`
having count(*) >= 2;

create index idx_deptName on dept(deptName);
create index idx_deptId on emp(deptId);
create index idx_CEO on dept(CEO);

# 衍生题
# 查出各个门派非掌门人的成员
# 优化前使用了子查询、关联查询
select * from t_emp e
where e.id not in (
	select CEO from t_dept d
 inner join t_emp e on e.id = d.CEO
)

# 优化后，只使用关联查询
select * from t_emp e 
left join t_dept d on e.id = d.ceo
where d.id is null

# 5-7省略优化，只是复习以前的知识点

# 8.显示每个门派年龄第二大的人
# 组内的名次
set @rank=0;
# 上一个部门id
set @last_deptid=0;
# 上一个成员的年龄，用来判断年龄相同的情况
set @last_age=0;

select e.deptid,e.name,e.age from
(
select e.*,
	# 组内排序，先判断上一个部门id与当前部门id是否相等，
	#	如果部门id相等，再判断上一个年龄与当前年龄是否相等，如果相等则rank不变，不相等则rank+1
	# 如果部门id不相等，则rank=1
	if(@last_deptid=deptid,if(@last_age=age,@rank,@rank:=@rank+1),@rank:=1) as rk,
		@last_deptid:=deptid as last_deptid,
		@last_age:=age as last_age
from t_emp e
order by deptid,age desc
)e where e.rk = 2