#1、列出自己的掌门比自己年龄小的人员
EXPLAIN SELECT a.name,a.age,c.name ceoname,c.age ceoage FROM t_emp a
LEFT JOIN t_dept b ON a.deptId=b.id
LEFT JOIN t_emp c ON b.CEO=c.id
WHERE a.age>c.age
#优化，给age字段添加索引
CREATE INDEX idx_age ON t_emp(age);
#2、列出所有年龄低于自己门派平均年龄的人员
SELECT c.name,c.age,aa.age avg_age FROM t_emp c INNER JOIN
(SELECT a.deptId,AVG(a.age)age FROM t_emp a
WHERE a.deptId IS NOT NULL
GROUP BY a.deptId)aa
ON c.deptId=aa.deptId
WHERE c.age<aa.age;
#优化建立索引，GROUP BY后面字段的索引和where 后面的索引
CREATE INDEX idx_deptid ON t_emp(deptid)
CREATE INDEX idx_deptid_age ON t_emp(deptId,age);
#3、列出至少有2个年龄大于40岁的成员的门派
SELECT b.deptName,COUNT(*) FROM t_emp a
INNER JOIN t_dept b ON a.deptId=b.id
WHERE a.age>40
GROUP BY b.deptName,b.id
HAVING count(*)>=2
#优化，建立组合索引
CREATE INDEX idx_deptid_age ON t_emp(deptId,age)
CREATE INDEX ids_deptname ON dept(deptName)
#4、至少有2位非掌门人成员的门派
SELECT c.deptName,c.id,COUNT(*) FROM t_emp a
INNER JOIN t_dept c ON a.deptId=c.id
LEFT JOIN t_dept b ON a.id=b.CEO WHERE b.id IS NULL
GROUP BY c.id,c.deptName
HAVING COUNT(*)>=2
#优化,建立索引
CREATE INDEX idx_ceo_deptname ON t_dept(ceo,deptName)
CREATE INDEX idx_deptname ON t_dept(deptName)
CREATE INDEX idx_deptid ON emp(deptid)
#5、列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否
CASE WHEN
IF
SELECT a.name, CASE WHEN b.id IS NULL THEN '否'else'是' END'是否为掌门'
FROM t_emp a LEFT JOIN t_dept b ON a.id=b.CEO
#6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50显示“老鸟”，否则显示“菜鸟”
SELECT b.deptName,IF(AVG(a.age)>50,'老鸟','菜鸟')'老鸟or菜鸟' FROM t_emp a
INNER JOIN t_dept b ON a.deptId=b.id
GROUP BY b.id,b.deptName
#7、显示每个门派年龄最大的人
SELECT a.`name`,a.age FROM t_emp a INNER JOIN
(SELECT deptId,MAX(age)max_age FROM t_emp
WHERE deptId IS NOT NULL
GROUP BY deptId)aa
ON a.age=aa.max_age AND a.deptId=aa.deptId
#优化
CREATE INDEX idx_deptid_age ON emp(deptid,age)
