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

--題1:求出通過住在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：求出通過住在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：找出訂購了產(chǎn)品p05的顧客的名字

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

--答案用最直接的SQL語句來解決該查詢問題

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

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

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

--那么我們來看一下三種情況的執(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 --清除緩存，以免下次計算時間  
declare @begin datetime  
declare @End datetime  
set @begin=getdate()  
exec a 'p05'  
set @End=getdate()  
select datediff(ms,@begin,@End) as 執(zhí)行時間(毫秒) 

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

--題5：要得到從代理商a03處訂購了產(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í)行效率高，還是只是該題的問題。
--題7：找出傭金百分率最小的代理商的aid值
select top(1) aid from agents order by [percent] --我能想到的就是排序然后取***個，但是我這樣做有問題，因為我求出來的只可能有 一個，而實際情況是可能有相同值的不止一個
--答案：

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é)果跟實際不符合
----我沒想出來，該怎么做？

--題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：求出所有滿足一下條件的顧客的cid值：該顧客的discnt值小于任一住在Duluth的顧客的discnt值
select cid from customers where discnt<any(select discnt from customers where city='Duluth') --這里是錯誤的，題目中的任一應(yīng)該是對應(yīng)所有的，所以應(yīng)把any改為all
--這種題目應(yīng)謹(jǐn)慎，留意

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

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

--正確答案：

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'  

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

--3.4.12 檢索沒有通過代理商a05訂貨的所有顧客的名字
select cid,cname from customers where cid not in (select cid from orders where aid='a05')
--這個時候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') 

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

--3.4.15檢索訂購了產(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 建立一個包含了顧客所在的或者代理商所在的或者兩者皆在的城市的名單

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

--3.5.2 求出通過住在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 并訂購了價格超過一美元的所有產(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 找出訂購了產(chǎn)品p01和價格超過1美元的所有產(chǎn)品的代理商的aid值
select aid from orders where dollars/qty>1 intersect select aid from orders where pid='p01' --并且或交集的意思在SQL里面如何表達？
select aid from orders where pid in (select pid from products where price>1 or pid='p01' )
--這顯然也是錯誤的，不是要它滿足某個條件就行，而是要同時包含這兩者。
--此題沒想出來
--可見，求交集的時候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訂購了某種產(chǎn)品，那要檢索的顧客也訂購了該產(chǎn)品

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

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

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的顧客訂購的產(chǎn)品的pid值

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

--同理：肯定是錯的，對待這種要包含的問題該如何寫sql語句
--答案：

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實踐性練習(xí)之子查詢的知識就介紹到這里了，希望本次的介紹能夠?qū)δ兴鶐椭?/p>

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

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

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

【編輯推薦】

1. SQL Server 2008數(shù)據(jù)庫學(xué)習(xí)筆記
2. SQL Server 2005數(shù)據(jù)庫nolock使用詳解
3. SQL Server如何啟用Ad Hoc Distributed Queries？
4. SQL Server 2008用存儲過程實現(xiàn)插入更新數(shù)據(jù)的實例
5. 含有GROUP BY子句的查詢中如何顯示COUNT()為0的結(jié)果