相關(guān)子查詢與非相關(guān)查詢.docx

相關(guān)子查詢 與非相關(guān)子查詢1：標(biāo)量子查詢(相對于多值子查詢)：只有標(biāo)量子查詢返回的是單個記錄或者不返回，就是有效的子查詢。Ex1:select OrderId From Orders where EmployeeId=(select EmployeeId From employees where lastName like NDavolio)將Davolio改為D%時,這個時候子查詢中返回結(jié)果為2行,等號右邊此時為多值,查詢失敗.將=改為in謂詞.查詢才能通過.employees表中無lastname=jason,外部查詢將返回null.2：非相關(guān)子查詢（嵌套子查詢）一個select.From.Where查詢語句塊可以嵌套在另一個select.From.Where查詢塊的Where子句中，稱為嵌套查詢。外層查詢稱為父查詢，主查詢。內(nèi)層查詢稱為子查詢，從查詢。子查詢可以嵌套多層，子查詢查詢到的結(jié)果又成為父查詢的條件。子查詢中不能有order by分組語句。先處理子查詢，再處理父查詢。 細分如下：1。 簡單嵌套查詢 查詢選修課程號為101并且成績高于學(xué)生號為9501101的所有學(xué)生的成績.select * from sclass where cno=101 and degree=(select degree from sclass where sno=9501101and cno=101)當(dāng)子查詢跟隨在 =、!=、= 之后， 子查詢的返回值只能是一個， 否則應(yīng)在外層where子句中用一個in限定符,即要返回多個值，要用in或者not in2。 帶not in的嵌套查詢 只要主查詢中列或運算式是在（不在）子查詢所得結(jié)果列表中的話，則主查詢的結(jié)果為我們要的數(shù)據(jù)selectsales_id,tot_amtfromsaleswheresale_idin(selectsale_idfromemployeewheresex=F)where sno not in(Select distinct sno from sclass)3。 帶exists的嵌套查詢子查詢的結(jié)果至少存在一條數(shù)據(jù)時，則主查詢的結(jié)果為我們要的數(shù)據(jù)。（exists）或自查詢的結(jié)果找不到數(shù)據(jù)時，則主查詢的結(jié)果為我們要的數(shù)據(jù)(notexists)我們經(jīng)常查詢的兩個表有多少重復(fù)的記錄就用這個以下范例讓你找出滯銷的產(chǎn)品，也就是尚未有任何銷售記錄的庫存產(chǎn)品。此范例主要是查詢以庫文件中的每一條產(chǎn)品代碼到銷售明細表中去查詢，如果查詢不到任何一條，表示該產(chǎn)品未曾賣出任何一件。select*fromstockawherenotexists(select*fromsale_d_id=d_idanda.stup_id=b.stup_id)4.select.where列或運算式比較運算運算【any|all(子查詢）只要主查詢中列或運算式與子查詢所得結(jié)果中任一（any）或全部（all）數(shù)據(jù)符合比較條件的話則主查詢的結(jié)果為我們要的數(shù)據(jù) selectsale_id,tot_amtfromsaleswheretot_amtany(selecttot_amtfromsaleswheresale_id=e0013andorder_date=1996/11/10)選出不同的人金額最高的訂單select*fromsalesawheretomat=(selectmax(totmat)fromsaleswherename=)3：相關(guān)子查詢（多值子查詢）1非相關(guān)子查詢是獨立于外部查詢的子查詢，子查詢總共執(zhí)行一次，執(zhí)行完畢后將值傳遞給外部查詢。 2相關(guān)子查詢的執(zhí)行依賴于外部查詢的數(shù)據(jù)，外部查詢執(zhí)行一行，子查詢就執(zhí)行一次。查詢中再查詢,通常是以一個查詢作為條件來供另一個查詢使用 例:有work表和部門表A:檢索出在部門表中登記的所有部門的職工基本資料select * from work where 部門編號 in not in(select 部門編號 from dbo.部門)B:檢索出在work表中每一個部門的最高基本工資的職工資料 select * from work a where 基本工資=(select max(基本工資) from work b where a.部門名稱=b.部門名稱) 說明:由外查詢提供一個部門名稱給內(nèi)查詢,內(nèi)查詢利用這個部門名稱找到該部門的最高基本工資,然后外查詢根據(jù)基本工資判斷是否等于最高工資,如果是的,則顯示出來. 相當(dāng)于:select * from work,(select 部門名稱,max(基本工資) as 基本工資 from work group by 部門名稱 as t) where work.基本工資=t.基本工資 and work.部門名稱=t.部門名稱 C:用嵌套work表和嵌套部門表,在嵌套work表中檢索出姓名和職工號都在嵌套部門存在的職工資料 select * from 嵌套work where 職工號 in (select 職工號 from 嵌套部門) and 姓名 in (select 姓名 from 嵌套部門) 察看結(jié)果,分析原因 （錯誤，因為這兩個in不是一對一 改:select * from 嵌套work a,嵌套部門 b where a.職工號=b.職工號 and a.姓名=b.姓名 改:select * from 嵌套work where 職工號=(select 職工號 from 嵌套部門) and 姓名=(select 姓名 from 嵌套部門) 行嗎?為什么,分析原因? 不能后面的select得到的結(jié)果不是一個值而又跟在=后必然出錯 在嵌套中使用exists關(guān)鍵字存在例:1:用嵌套work表和嵌套部門表,在嵌套work表中檢索出姓名和職工號都在嵌套部門存在的職工資料 select * from 嵌套work a where exists (select * from 嵌套部門 b where a.姓名=b.姓名 and a.職工號=b.職工號) 2:在work表檢索出在部門表沒有的職工 select * from work where not exists (select * from 部門 where 部門.部門編號=work.部門編號) 能否改成:select * from work where exists (select * from 部門 where 部門.部門編號work.部門編號) 是不能的，在列清單中使用select例:1:在work1表和部門表中檢索出所有部門的部門名稱和基本工資總和 select 部門名稱,(select sum(基本工資) from work1 b where a.部門編號=b.部門編號) from 部門 a 2:檢索各部門的職工人數(shù) select 部門編號,部門名稱,(select count(職工號) from work1 a where a.部門編號=b.部門編號) as 人數(shù) from 部門 b 3:在商品表和銷售表中查詢每一職工的姓名,所屬部門,銷售總量 select 姓名,所屬部門,(select sum(銷售量) from 商品銷售 a where a.職工號=b.職工號) as 銷售總量 from 嵌套部門 b說明：都是相關(guān)子查詢的特殊情況，外層的查詢是內(nèi)層查詢的條件如：a.職工號=b.職工號，內(nèi)層條件成立則得到的些行記錄是并入外層查詢的最終結(jié)果，否則不記錄入最后結(jié)果 許多查詢都可以通過執(zhí)行一次子查詢并將得到的值代入外部查詢的 WHERE 子句中進行計算。在包括相關(guān)子查詢（也稱為重復(fù)子查詢）的查詢中，子查詢依靠外部查詢獲得值。這意味著子查詢是重復(fù)執(zhí)行的，為外部查詢可能選擇的每一行均執(zhí)行一次。此查詢在 SalesPerson 表中檢索獎金為 5000 且雇員標(biāo)識號與 Employee 和 SalesPerson 表中的標(biāo)識號相匹配的雇員的名和姓的一個實例。USE AdventureWorks2008R2;GOSELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID FROM Person.Person AS c JOIN HumanResources.Employee AS eON e.BusinessEntityID = c.BusinessEntityID WHERE 5000.00 IN (SELECT Bonus FROM Sales.SalesPerson sp WHERE e.BusinessEntityID = sp.BusinessEntityID) ;GO下面是結(jié)果集：LastName FirstName BusinessEntityID- - -Ansman-Wolfe Pamela 280Saraiva Jos 282(2 row(s) affected)該語句中前面的子查詢無法獨立于外部查詢進行計算。它需要 Employee.BusinessEntityID 值，但是此值隨 SQL Server 檢查 Employee 中的不同行而改變。下面準(zhǔn)確說明了如何計算此查詢：SQL Server 通過將每一行的值代入內(nèi)部查詢，考慮 Employee 表中的每一行是否都包括在結(jié)果中。例如，如果 SQL Server 首先檢查 Syed Abbas 行，那么變量 Employee.BusinessEntityID 將取值 285，SQL Server 將該值代入內(nèi)部查詢。USE AdventureWorks2008R2;GOSELECT BonusFROM Sales.SalesPersonWHERE BusinessEntityID = 285;結(jié)果為 0（Syed Abbas 沒有收到獎金，因為他不是銷售人員），因此外部查詢計算為：USE AdventureWorks2008R2;GOSELECT LastName, FirstNameFROM Person.Person AS c JOIN HumanResources.Employee AS eON e.BusinessEntityID = c.BusinessEntityID WHERE 5000 IN (0.00)引用外部查詢列的子查詢。邏輯上講，子查詢會為外部查詢的每行計算一次。Ex4:查詢每個員工employee最近的一個訂單思路:-需要附加屬性(Tiebreaker)Max(OrderDate)最大訂單日期和Max(OrderId)最大訂單號來確定每名員工的最近訂單狀況select OrderId,CustomerId,EmployeeId,OrderDate From Orders As o1 where Ord