SQL Server实践性练习之子查询实例

上次我们介绍了：SQL Server实践性练习之创建库表及条件查询，本次我们来介绍一下SQL Server数据库子查询的一些实践性练习的实例，接下来就让我们来一起了解一下这部分内容。

--题1:求出通过住在Duluth和Dallas的代理商订了货的顾客的cid值

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

--题2:检索有关住在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：找出订购了产品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'; 

--那么我们来看一下三种情况的执行效率

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 执行时间(毫秒) 

--由此可见，一般情况下这种题目能直接写的就直接用连接的方法，用in的效率极低

--题5：要得到从代理商a03处订购了产品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执行效率高，还是只是该题的问题。
--题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 --该方法得出的结果跟实际不符合
----我没想出来，该怎么做？

--题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')
--执行效率：in 3ms,some 6ms,难道in 的执行效率比some高？

--题10：求出所有满足一下条件的顾客的cid值：该顾客的discnt值小于任一住在Duluth的顾客的discnt值
select cid from customers where discnt<any(select discnt from customers where city='Duluth') --这里是错误的，题目中的任一应该是对应所有的，所以应把any改为all
--这种题目应谨慎，留意

--题11：检索通过代理商a05订货的所有顾客的名字
select cname from customers where cid in (select cid from orders where aid='a05' )
--总结，凡是这种题目，都可以直接做取别名，或连接或in，但是in的效率***

----题12：求出既订购了产品p01又订购了产品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关键字

--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检索订购了产品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 并订购了价格超过一美元的所有产品的代理商的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 找出订购了产品p01和价格超过1美元的所有产品的代理商的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 找出具有以下性质的顾客的cid 值：如果顾客c006订购了某种产品，那要检索的顾客也订购了该产品

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

--跟答案不符,那么该怎么写呢？问题还是应该为包含，而不是在其中满足某个条件
--答案：

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的顾客订购的产品的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)  
) 

关于SQL Server实践性练习之子查询的知识就介绍到这里了，希望本次的介绍能够对您有所帮助。

SQL Server实践性练习系列的文章：

SQL Server实践性练习之高级SQL查询

SQL Server实践性练习之创建库表及条件查询

【编辑推荐】

1. SQL Server 2008数据库学习笔记
2. SQL Server 2005数据库nolock使用详解
3. SQL Server如何启用Ad Hoc Distributed Queries？
4. SQL Server 2008用存储过程实现插入更新数据的实例
5. 含有GROUP BY子句的查询中如何显示COUNT()为0的结果