多行子查詢

集合比較子查詢

內(nèi)查詢返回多行數(shù)據(jù)

我們這個(gè)時(shí)候使用=這個(gè)符號(hào)就不會(huì)合適了,多行子查詢用到的符號(hào)應(yīng)該是

多行比較操作符

ANY是和其中任一個(gè)值比較,ALL是和所有值比較

比如現(xiàn)在有女生是家務(wù)做的很好,美麗的,賢惠的,暴力了,ANY就是選其中一個(gè)就行,ALL就是全部都要

SOME實(shí)際是ANY的別名,和ANY的作用一致

多行子查詢的操作符:IN,ANY,ALL,SOME(ANY)

操作符:IN

我們這里用等于是不行的,所以改為IN

這里的需求就是查詢首字母大于a的姓名和工作.當(dāng)然我們前面的等號(hào)都可以替換為IN,因?yàn)楹竺媸抢ㄌ?hào)嘛

操作符:ANY

題目1返回job_id中比job_id為IT_PROG部門任一工資低的員工的員工號(hào),姓名,job_id,salary

WHERE job_id IN (SELECT job_id FROM employees WHERE last_name>'a')
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(SELECT salary
                  FROM employees
                  WHERE job_id='IT_PROG')
AND job_id<>'IT_PROG'

結(jié)果里有8000也有4000,因?yàn)橹灰绕渲幸粋€(gè)人低就可以了,也就是比最大值低就行

這是ANY函數(shù)的用法,我們再看看ALL的情況

操作符:ALL

題目2””返回job_id中比job_id為IT_PROG部門所有工資低的員工的員工號(hào),姓名,job_id,salary

這里我們就用到ALL函數(shù)

WHERE job_id IN (SELECT job_id FROM employees WHERE last_name>'a')
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(SELECT salary
                  FROM employees
                  WHERE job_id='IT_PROG')
AND job_id<>'IT_PROG'

這里的值就少了很多,因?yàn)橛玫氖茿LL函數(shù),所以結(jié)果必須比全部部門是’it_prog’部門工資低,所以最后的結(jié)果是小于最小值才行,條件上ALL是比較苛刻的,

大家體會(huì)一下這兩個(gè)函數(shù)的區(qū)別

一個(gè)練習(xí)

題目:查詢平均工資最低的部門ID

這個(gè)看起來簡單,實(shí)際有點(diǎn)難度,我們先把條件先把這個(gè)條件都分成很多步,我們先一步一步做,然后最后豁然開朗

我們先查各個(gè)部門的平均工資

SELECT AVG(salary)
                  FROM employees
                  GROUP BY department_id

我們比每一個(gè)都小是不是可以直接用MIN,這是不可以的,因?yàn)榫酆虾瘮?shù)是不能嵌套的,mysql是不支持聚合函數(shù)嵌套的,不過Oracle是支持的,所以之后版本可能會(huì)支持.但是到9.0為止是還不支持的.

我們想想能不能用ANY和ALL解決,我們這個(gè)部門的工資現(xiàn)在是不是要比每一個(gè)部門的工資都要小,但是和最后一個(gè)是相等的,那么我們就可以用<=里面的每一個(gè)值.:這是方法1:

SELECT department_id
FROM employees
WHERE salary<=ALL(SELECT AVG(salary)
                  FROM employees
                  GROUP BY department_id )

這樣寫是錯(cuò)誤的,因?yàn)槲覀円蟮氖瞧骄べY小于,不能用WHERE,要用GROUP BY和HAVING組合起來使用,這個(gè)寫法才是正確的,這個(gè)方法比較的簡便.

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(SELECT AVG(salary)
                  FROM employees
                  GROUP BY department_id )

還有方法2,我們看(SELECT AVG(salary)

                  FROM employees

                  GROUP BY department_id這個(gè)是不是代表了一個(gè)表,那么我們希望從這個(gè)表里提取出這行的最小值,自然而然的我們直接FROM這張表,并用MIN函數(shù),我們試一試 

SELECT avg_salary          
  FROM(SELECT AVG(salary)
                  FROM employees
                  GROUP BY department_id )

結(jié)果報(bào)錯(cuò)了,他所,我們需要給這個(gè)表一個(gè)別名,順便給字段也來一個(gè)別名不然容易()出錯(cuò)

查出來之后我們就可以用MIN嵌套

所以這里我們也用了一個(gè)子查詢,還是比較有意思的,接下來我們要查那個(gè)部門的工資等于這個(gè)值

這里我們就用到了三層的子查詢.前面的方法比較簡便一些.這個(gè)方法比較容易理解.

空值問題

我們想查詢是管理者的員工.也就是員工id等于部門id的員工有那幾個(gè)

SELECT last_name
FROM employees
WHERE employee_id IN(SELECT manager_id
                      FROM employees)

現(xiàn)在一共查出來18條,那么我們還有一個(gè)疑惑,可不可以查出來剩下不是管理者的員工呢,我們把IN改成NOT IN

SELECT last_name
FROM employees
WHERE employee_id NOT IN(SELECT manager_id
                      FROM employees)

發(fā)現(xiàn)結(jié)果是空表,不是我們期望的結(jié)果,這里不能簡單的加個(gè)NOT,這是為什么呢,因?yàn)槲覀兊慕Y(jié)果里包含了NULL

SELECT last_name
FROM employees
WHERE employee_id NOT IN(SELECT manager_id
                      FROM employees
                      WHERE manager_id IS NOT NULL)

我們添加一個(gè)結(jié)果不為空的條件,就可以得到89條記錄

這是因?yàn)閮?nèi)查詢有個(gè)NULL值,這里比較特別,IN的時(shí)候是可以出結(jié)果的,NOT IN就不能了,其實(shí)也好理解,因?yàn)榭沾砦粗?也就是語句不知道這個(gè)值是多少,所以不包含這個(gè)值是不確定的.任何值都可能是空,所以它就返回了一個(gè)空表.而IN有準(zhǔn)確的結(jié)果,就不會(huì)返回空表了

這塊多表查詢就給大家講到這里

總結(jié)

到此這篇關(guān)于mysql多行子查詢(只包含不相關(guān)子查詢)的文章就介紹到這了,更多相關(guān)mysql多行子查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論