MySQL12 - 子查询

子查询

可以在哪里使用子查询？

• SELECT .. (SELECT) ..
• FROM .. (SELECT) ..
• WHERE .. (SELECT) ..

WHERE 子句中的子查询

例子：找出比最低工资高的员工姓名和工资

• WHERE 子句中不能使用分组函数 MIN()

• 因此，1 查询最低工资

  • SELECT
    	MIN(sal)
    FROM
    	emp;
    

• 2 找出大于最低工资的

  • SELECT
    	ename.sal
    FROM
    	emp
    WHERE
    	sal > 800;
    

• 3 合并

  • SELECT
    	e.ename, e.sal
    FROM
    	emp e
    WHERE
    	sal > (SELECT
    				MIN(sal)
    			FROM
    				emp);
    

FROM 子句中的子查询

FROM后的子查询可以将子查询的结果当作一张临时表

例子：找出每个岗位的平均工资的薪资等级

• 1 找出每个岗位的平均工资

  • SELECT
    	job, AVG(sal)
    FROM
    	emp
    GROUP BY
    	job;
    

• 2 将得到的平均工资与薪资等级相匹配

  • SELECT
    	job, avg_sal, grade
    FROM
    	t 
    JOIN
    	salgrade AS s
    ON
    	t.avg_sal > s.losal AND t.avg_sal < s.hisal
    ORDER BY
    	avg_sal ASC, job ASC;
    

• 3 合并

  • SELECT
    	job, avg_sal, grade
    FROM
    	(SELECT
    		job, AVG(sal) AS avg_sal
    	FROM
    		emp
    	GROUP BY
    		job) AS t 
    JOIN
    	salgrade AS s
    ON
    	t.avg_sal BETWEEN s.losal AND s.hisal
    ORDER BY
    	avg_sal ASC, job ASC;
    

SELECT 子句中的子查询 -- 了解即可

只要子查询多于一条记录就报错，不推荐使用， JOIN ON 语句可以代替