SQL Server實(shí)踐性練習(xí)之子查詢(xún)實(shí)例

作者：凌寒獨(dú)自開(kāi) 2011-08-12 09:29:32

本文我們主要介紹了一些SQL Server數(shù)據(jù)庫(kù)實(shí)踐性練習(xí)之子查詢(xún)的實(shí)例，通過(guò)這些實(shí)例我們可以迅速掌握SQL Server數(shù)據(jù)庫(kù)的子查詢(xún)，希望能夠?qū)δ兴鶐椭?/div>

上次我們介紹了：SQL Server實(shí)踐性練習(xí)之創(chuàng)建庫(kù)表及條件查詢(xún)，本次我們來(lái)介紹一下SQL Server數(shù)據(jù)庫(kù)子查詢(xún)的一些實(shí)踐性練習(xí)的實(shí)例，接下來(lái)就讓我們來(lái)一起了解一下這部分內(nèi)容。

--題1:求出通過(guò)住在Duluth和Dallas的代理商訂了貨的顧客的cid值

select distinct cid from orders where aid in (select aid from agents where city='Duluth' or city='Dallas' )

--題2:檢索有關(guān)住在Duluth或Dallas的代理商的所有信息

select * from agents where city='Duluth' or city='Dallas'

--題3：求出通過(guò)住在Duluth或Dallas的代理商訂貨的所有顧客的姓名和折扣

select cname,discnt from customers where cid in (select cid from orders where aid in (select aid from agents where city='Duluth' or city='Dallas') )

--或者

select cname,discnt from customers where cid in (select cid from orders where aid in (select aid from agents where city in ('Duluth' ,'Dallas')))

--題4：找出訂購(gòu)了產(chǎn)品p05的顧客的名字

select cname from customers where cid in (select cid from orders where pid='p05')

--答案用最直接的SQL語(yǔ)句來(lái)解決該查詢(xún)問(wèn)題

select distinct cname from customers,orders where customers.cid = orders.cid and orders.pid='p05';

--用連接也能達(dá)到相同的效果，重要的是拆解題目的意思

select distinct cname from customers inner join orders on customers.cid = orders.cid and orders.pid='p05';

--那么我們來(lái)看一下三種情況的執(zhí)行效率

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:<Author,,Name> 
-- Create date: <Create Date,,> 
-- Description:<Description,,> 
-- =============================================  
alter PROCEDURE a  
@pid varchar(10)  
AS  
BEGIN  
--select cname from customers where cid in (select cid from orders where pid=@pid) 16ms  
--select distinct cname from customers,orders where customers.cid = orders.cid and orders.pid=@pid; 3ms  
--select distinct cname from customers inner join orders on customers.cid = orders.cid and orders.pid=@pid; 3ms  
END  
GO  
DBCC FREEPROCCACHE --清除緩存，以免下次計(jì)算時(shí)間  
declare @begin datetime  
declare @End datetime  
set @begin=getdate()  
exec a 'p05'  
set @End=getdate()  
select datediff(ms,@begin,@End) as 執(zhí)行時(shí)間(毫秒)

--由此可見(jiàn)，一般情況下這種題目能直接寫(xiě)的就直接用連接的方法，用in的效率極低

--題5：要得到從代理商a03處訂購(gòu)了產(chǎn)品p07的顧客的名字

select cname from customers inner join orders on customers.cid =orders.cid and aid='a03' and pid='p07' 
select cname from customers where cid in (select cid from orders where aid='a03' and pid='p07')

--題6：檢索由住在Duluth的顧客和住在New York 的代理商組成的所有訂貨記錄的ordno值

select ordno from orders where cid in (select cid from customers where city='Duluth') and aid in (select aid from agents where city='New York') --6ms

--答案：

select ordno from orders x where exists (select cid,aid from customers c,agents a   
where c.cid=x.cid and a.aid=x.aid and c.city='Duluth' and a.city='New York') --10ms

--疑惑：難道in比exists執(zhí)行效率高，還是只是該題的問(wèn)題。
--題7：找出傭金百分率最小的代理商的aid值
select top(1) aid from agents order by [percent] --我能想到的就是排序然后取***個(gè)，但是我這樣做有問(wèn)題，因?yàn)槲仪蟪鰜?lái)的只可能有一個(gè)，而實(shí)際情況是可能有相同值的不止一個(gè)
--答案：

select aid from agents where [percent]<=all(select [percent] from agents)

----題8：找出住在Dallas或Boston的顧客擁有相同折扣的所有顧客
--select c1.cname ,c2.cname from customers c1,customers c2 where c1.discnt=c2.discnt and c1.cid<c2.cid --該方法得出的結(jié)果跟實(shí)際不符合
----我沒(méi)想出來(lái)，該怎么做？

--題9：找出與住在Dallas或Boston的顧客擁有相同折扣的所有顧客
select cid,cname from customers where discnt in (select discnt from customers where city='Dallas' or city='Boston')
--答案：
select cid,cname from customers where discnt=some(select discnt from customers where city='Dallas' or city='Boston')
--執(zhí)行效率：in 3ms,some 6ms,難道in 的執(zhí)行效率比some高？

--題10：求出所有滿(mǎn)足一下條件的顧客的cid值：該顧客的discnt值小于任一住在Duluth的顧客的discnt值
select cid from customers where discnt<any(select discnt from customers where city='Duluth') --這里是錯(cuò)誤的，題目中的任一應(yīng)該是對(duì)應(yīng)所有的，所以應(yīng)把a(bǔ)ny改為all
--這種題目應(yīng)謹(jǐn)慎，留意

--題11：檢索通過(guò)代理商a05訂貨的所有顧客的名字
select cname from customers where cid in (select cid from orders where aid='a05' )
--總結(jié)，凡是這種題目，都可以直接做取別名，或連接或in，但是in的效率***

----題12：求出既訂購(gòu)了產(chǎn)品p01又訂購(gòu)了產(chǎn)品p07的顧客的cid值
--select cid from orders where pid='p01'
--select cid from orders where pid='p07'
----然后求上面兩式的交集，我沒(méi)做出來(lái)
--select distinct cid from orders where pid='p07' and exists (select cid from orders where pid='p01' )
----這樣做雖然答案正確，但是換位置之后就有錯(cuò)誤了
--遇到這種問(wèn)題的思路是什么樣的？

--正確答案：

select distinct cid from orders x  
where pid='p01' and exists (select * from orders where cid=x.cid and pid='p07')

--為什么這里一定要取別名
--取別名除了有方便的好處外，有什么情況是必須用到的嗎？

select cid from orders where pid='p01' intersect select cid from orders where pid='p07'

--注：兩個(gè)的交集，可以用intersect關(guān)鍵字

--3.4.12 檢索沒(méi)有通過(guò)代理商a05訂貨的所有顧客的名字
select cid,cname from customers where cid not in (select cid from orders where aid='a05')
--這個(gè)時(shí)候in 不能用exists 代替
----答案：

select distinct c.cid ,c.cname from customers c   
where not exists (select * from orders x where c.cid=x.cid and x.cid='a05')

----實(shí)際上答案是錯(cuò)的，但是中文解釋好像又能夠解釋通，為什么呢？

--3.4.15檢索訂購(gòu)了產(chǎn)品p01的顧客所在的city

select cname,city from customers where cid in (select cid from orders where pid='p01')  
select distinct cname,city from customers inner join orders on customers.cid=orders.cid and orders.pid='p01'

--3.5.1 建立一個(gè)包含了顧客所在的或者代理商所在的或者兩者皆在的城市的名單

select distinct city from agents union (select city from customers)

--3.5.2 求出通過(guò)住在New York的所有代理商訂了貨的顧客的cid值

select distinct cid from orders where aid in (select aid from agents where city='New York' )

--3.5.3 求出住在New York 或Duluth 并訂購(gòu)了價(jià)格超過(guò)一美元的所有產(chǎn)品的代理商的aid值

select aid from agents where aid in (select aid from orders where dollars/qty>1) and city='New York' or city='Duluth'

--3.5.4 找出訂購(gòu)了產(chǎn)品p01和價(jià)格超過(guò)1美元的所有產(chǎn)品的代理商的aid值
select aid from orders where dollars/qty>1 intersect select aid from orders where pid='p01' --并且或交集的意思在SQL里面如何表達(dá)？
select aid from orders where pid in (select pid from products where price>1 or pid='p01' )
--這顯然也是錯(cuò)誤的，不是要它滿(mǎn)足某個(gè)條件就行，而是要同時(shí)包含這兩者。
--此題沒(méi)想出來(lái)
--可見(jiàn)，求交集的時(shí)候intersect的重要性。

--答案：

select y.aid from orders y where y.pid='p01' and not exists (select p.pid from products p where p.price>1.0000 and   
not exists (select * from orders x where x.pid=p.pid and x.aid=y.aid))

--3.5.5 找出具有以下性質(zhì)的顧客的cid 值：如果顧客c006訂購(gòu)了某種產(chǎn)品，那要檢索的顧客也訂購(gòu)了該產(chǎn)品

select cname,cid from customers where cid in (select cid from orders where pid in (select pid from orders where cid='c006'))

--跟答案不符,那么該怎么寫(xiě)呢？問(wèn)題還是應(yīng)該為包含，而不是在其中滿(mǎn)足某個(gè)條件
--答案：

select cid from customers c where not exists (select z.pid from orders z   
where z.cid='c006' and not exists (select * from orders y where y.pid=z.pid and y.cid=c.cid)  
)

--3.5.6 找出被所有住在Duluth的顧客訂購(gòu)的產(chǎn)品的pid值

select distinct pid from orders where cid in (select cid from customers where city='Duluth' )

--同理：肯定是錯(cuò)的，對(duì)待這種要包含的問(wèn)題該如何寫(xiě)sql語(yǔ)句
--答案：

select pid from products p where not exists (select c.cid from customers c where c.city='Duluth' 
and not exists (select * from orders x where x.pid=p.pid and x.cid=c.cid)  
)

關(guān)于SQL Server實(shí)踐性練習(xí)之子查詢(xún)的知識(shí)就介紹到這里了，希望本次的介紹能夠?qū)δ兴鶐椭?/p>

SQL Server實(shí)踐性練習(xí)系列的文章：

SQL Server實(shí)踐性練習(xí)之高級(jí)SQL查詢(xún)

SQL Server實(shí)踐性練習(xí)之創(chuàng)建庫(kù)表及條件查詢(xún)

【編輯推薦】