20_MySQL表的内连接实操

-- 查询底薪超过公司平均底薪的员工信息

-- 方法1
SELECT empno,ename
FROM t_emp
HAVING sal>AVG(sal);

因为在having中不能拿一个字段与聚合函数比较，所以此种方法行不通

-- 方法2

SELECT e1.empno,e1.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.empno=e2.empno
WHERE e1.sal>AVG(e2.sal);

错误：非法使用聚合函数

-- 方法3

SELECT e.empno,e.ename,e.sal
FROM t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t
ON e.sal >= t.avg;
一个结果也能够作为一张表与另一张表进行关联

-- 查询research部门的人数，最高底薪，最低底薪，平均底薪，平均工龄

SELECT 
    count(*),
    MAX(e.sal),
    MIN(e.sal),
    AVG(e.sal),
    AVG(DATEDIFF(NOW(),hiredate)/365)
FROM 
    t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE 
    d.dname="RESEARCH";

-- 查询每种职业的最高工资、最低工资、平均工资、最高工资等级和最低工资等级

SELECT 
    e.job,
    MAX(e.sal+IFNULL(e.comm,0)),
    MIN(e.sal+IFNULL(e.comm,0)),
    AVG(e.sal+IFNULL(e.comm,0)),
    MAX(s.grade),
    MIN(s.grade)
FROM t_emp e JOIN t_salgrade s 
ON (e.sal+IFNULL(e.comm,0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

-- 查询每个底薪超过部门平均底薪的员工信息

SELECT e.deptno,e.ename,e.sal,d.avg
FROM t_emp e JOIN (SELECT deptno,AVG(sal) AS avg FROM t_emp GROUP BY deptno) d
ON e.deptno=d.deptno
WHERE sal >= d.avg
ORDER BY e.deptno;