MySQL子查詢與HAVING/SELECT的結(jié)合使用

更新時間：2023年06月25日 14:35:55 作者：熱愛編程的小K

這篇文章主要介紹了MySQL子查詢在HAVING/SELECT字句中使用、及相關(guān)子查詢和WITH/EXISTS字句的使用，具有一定的參考價值，感興趣的可以了解一下

前言

本節(jié)將為大家?guī)鞰ySQL子查詢在HAVING/SELECT字句中使用、及相關(guān)子查詢和WITH/EXISTS字句的講解?

一、在HAVING/SELECT字句中使用子查詢

??HAVING字句

查詢部門編號、員工人數(shù)、平均工資，并且要求這些部門的平均工資高于公司平均薪資。

SELECT deptno,COUNT(deptno) cnt,AVG(sal) avgsal 
FROM emp 
GROUP BY deptno
HAVING avgsal>
(
	SELECT AVG(sal) FROM emp
);

查詢出所有部門中平均工資最高的部門名稱及平均工資

SELECT e.deptno,d.dname,ROUND(AVG(sal),2) avgsal
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno
HAVING avgsal>
(
    #查詢出所有部門平均工資中最高的薪資
	 SELECT MAX(avgsal) FROM 
		(SELECT AVG(sal) avgsal FROM emp GROUP BY deptno) AS temp
)

??SELECT字句

查詢出公司每個部門的編號、名稱、位置、部門人數(shù)、平均工資

#1多表查詢
SELECT d.deptno,d.dname,d.loc,COUNT(e.deptno),AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno;
#2
SELECT d.deptno,d.dname,d.loc,temp.cnt,temp.avgsal
FROM dept d,(SELECT deptno,COUNT(deptno) cnt,AVG(sal) avgsal FROM emp GROUP BY deptno) temp
WHERE d.deptno=temp.deptno;
#3 關(guān)聯(lián)子查詢
SELECT d.deptno,d.dname,d.loc,
(SELECT COUNT(deptno) FROM emp WHERE deptno=d.deptno GROUP BY deptno) cnt,
(SELECT AVG(sal) FROM emp WHERE deptno=d.deptno GROUP BY deptno) avgsal
FROM dept d;

二、相關(guān)子查詢

?如果子查詢的執(zhí)行依賴外部查詢，通常情況下都是因為子查詢中的表用到了外部的表，并進(jìn)行了條件關(guān)聯(lián)，因此每執(zhí)行一次外部查詢，子查詢都要重新計算一次，這樣的子查詢就成為關(guān)聯(lián)子查詢。相關(guān)子查詢按照一行接一行的順序指針，主查詢的每一行都指向一次子查詢。

?查詢需求

查詢員工中工資大于本部門平均工資的員工的部門編號、姓名、薪資

SELECT e.deptno,e.ename,e.sal
FROM emp e
WHERE e.sal>(SELECT AVG(sal) FROM emp WHERE deptno=e.deptno );

三、WITH/EXISTS、NOT EXISTS字句

??WITH字句

查詢每個部門的編號、名稱、位置、部門平均工資、人數(shù)

-- 多表查詢
SELECT d.deptno,d.dname,d.loc,AVG(e.sal) avgsal ,COUNT(e.deptno) cnt
FROM dept d,emp e
WHERE d.deptno=e.deptno
GROUP BY e.deptno;
-- 子查詢
SELECT d.deptno,d.dname,d.loc,temp.avgsal,temp.cnt
FROM dept d,(
							SELECT deptno,AVG(sal) avgsal,COUNT(deptno) cnt
							FROM emp
							GROUP BY deptno
						)temp
WHERE d.deptno=temp.deptno;
-- 使用with
WITH temp AS(
							SELECT deptno,AVG(sal) avgsal,COUNT(deptno) cnt
							FROM emp
							GROUP BY deptno
						)
SELECT d.deptno,d.dname,d.loc,temp.avgsal,temp.cnt
FROM dept d,temp
WHERE d.deptno=temp.deptno;

查詢每個部門工資最高的員工編號、姓名、職位、雇傭日期、工資、部門編號、部門名稱，顯示的結(jié)果按照部門編號進(jìn)行排序

-- 相關(guān)子查詢
SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND e.sal=(SELECT MAX(sal) FROM emp WHERE deptno=e.deptno)
ORDER BY e.deptno;
-- 表子查詢
SELECT e.empno,e.ename,e.job,e.hiredate,e.sal,e.deptno,d.dname
FROM emp e,dept d,(SELECT deptno,MAX(sal) maxsal FROM emp GROUP BY deptno) temp
WHERE e.deptno=d.deptno
AND e.sal=temp.maxsal
AND e.deptno = temp.deptno
ORDER BY e.deptno;

??EXISTS/NOT EXISTS字句

在SQL中提供了一個exixts結(jié)構(gòu)用于判斷子查詢是否有數(shù)據(jù)返回。如果子查詢中有數(shù)據(jù)返回，exists結(jié)構(gòu)返回true，否則返回false。

查詢公司管理者的編號、姓名、工作、部門編號

-- 多表查詢
SELECT DISTINCT e.empno,e.ename,e.job,e.deptno
FROM emp e JOIN emp mgr
ON e.empno=mgr.mgr;
-- 使用EXISTS
SELECT e.empno,e.ename,e.job,e.deptno
FROM emp e
WHERE EXISTS (SELECT * FROM emp WHERE e.empno=mgr);

查詢部門表中，不存在于員工表中的部門信息

-- 多表查詢
SELECT e.deptno,d.deptno,d.dname,d.loc
FROM emp e RIGHT JOIN dept d
ON e.deptno=d.deptno
WHERE e.deptno IS NULL;
-- 使用EXISTS
SELECT d.deptno,d.dname,d.loc
FROM  dept d
WHERE NOT EXISTS (SELECT deptno FROM emp WHERE deptno=d.deptno);