多行子查詢

集合比較子查詢

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

我們這個時候使用=這個符號就不會合適了,多行子查詢用到的符號應(yīng)該是

多行比較操作符

ANY是和其中任一個值比較,ALL是和所有值比較

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

SOME實際是ANY的別名,和ANY的作用一致

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

操作符:IN

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

這里的需求就是查詢首字母大于a的姓名和工作.當然我們前面的等號都可以替換為IN,因為后面是括號嘛

操作符:ANY

題目1返回job_id中比job_id為IT_PROG部門任一工資低的員工的員工號,姓名,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,因為只要比其中一個人低就可以了,也就是比最大值低就行

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

操作符:ALL

題目2””返回job_id中比job_id為IT_PROG部門所有工資低的員工的員工號,姓名,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'

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

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

一個練習

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

這個看起來簡單,實際有點難度,我們先把條件先把這個條件都分成很多步,我們先一步一步做,然后最后豁然開朗

我們先查各個部門的平均工資

SELECT AVG(salary)
                  FROM employees
                  GROUP BY department_id

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

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

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

這樣寫是錯誤的,因為我們要求的是平均工資小于,不能用WHERE,要用GROUP BY和HAVING組合起來使用,這個寫法才是正確的,這個方法比較的簡便.

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這個是不是代表了一個表,那么我們希望從這個表里提取出這行的最小值,自然而然的我們直接FROM這張表,并用MIN函數(shù),我們試一試 

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

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

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

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

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

空值問題

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

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

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

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

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

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

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

這是因為內(nèi)查詢有個NULL值,這里比較特別,IN的時候是可以出結(jié)果的,NOT IN就不能了,其實也好理解,因為空代表未知,也就是語句不知道這個值是多少,所以不包含這個值是不確定的.任何值都可能是空,所以它就返回了一個空表.而IN有準確的結(jié)果,就不會返回空表了

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

總結(jié)

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

最新評論