mysql子查询

最新推荐文章于 2024-08-19 16:45:00 发布

蔚蓝天空857

最新推荐文章于 2024-08-19 16:45:00 发布

阅读量87

点赞数

文章标签： sql mysql plsql

本文链接：https://blog.csdn.net/qq_45148387/article/details/116350935

版权

select语句当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在哪里：
select
·······（select）
from
·······（select）
where
········（select）

案例（where字句中使用子查询）找出高于平均薪资的员工信息。

mysql> select * from emp where sal > (select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

案例（from后面嵌套子查询）：找出每个部门平均薪水的薪资等级。

第一步：找出每个部门平均薪水（按照部门编号分组，求出sal的平均值）

mysql> select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

第二步：将以上的查询结果作为临时表t，让表t和salgrade s表连接，条件是t.avgsal between s.losal and s,hisal

mysql> select t.*,s.grade from ( select deptno,avg(sal) as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+