SQLServer实践性练习之子查询实例

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

在宜黄等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供成都做网站、成都网站建设、成都外贸网站建设 网站设计制作按需求定制设计,公司网站建设,企业网站建设,成都品牌网站建设,成都营销网站建设,外贸营销网站建设,宜黄网站建设费用合理。

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

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

--题2:检索有关住在Duluth或Dallas的代理商的所有信息

 
 
 
    
  
  
  
  1. select * from agents where city='Duluth' or city='Dallas' 
    2.

--题3：求出通过住在Duluth或Dallas的代理商订货的所有顾客的姓名和折扣

 
 
 
    
  
  
  
  1. 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') ) 
    2.

--或者

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

--题4：找出订购了产品p05的顾客的名字

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

--答案用最直接的SQL语句来解决该查询问题

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

--用连接也能达到相同的效果，重要的是拆解题目的意思

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

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

 
 
 
    
  
  
  
  1. SET ANSI_NULLS ON  
    2. 
  
  
  
  2. GO  
    3. 
  
  
  
  3. SET QUOTED_IDENTIFIER ON  
    4. 
  
  
  
  4. GO  
    5. 
  
  
  
  5. -- =============================================  
    6. 
  
  
  
  6. -- Author: 
    7. 
  
  
  
  7. -- Create date:  
    8. 
  
  
  
  8. -- Description: 
    9. 
  
  
  
  9. -- =============================================  
    10. 
  
  
  
  10. alter PROCEDURE a  
    11. 
  
  
  
  11. @pid varchar(10)  
    12. 
  
  
  
  12. AS  
    13. 
  
  
  
  13. BEGIN  
    14. 
  
  
  
  14. --select cname from customers where cid in (select cid from orders where pid=@pid) 16ms  
    15. 
  
  
  
  15. --select distinct cname from customers,orders where customers.cid = orders.cid and orders.pid=@pid; 3ms  
    16. 
  
  
  
  16. --select distinct cname from customers inner join orders on customers.cid = orders.cid and orders.pid=@pid; 3ms  
    17. 
  
  
  
  17. END  
    18. 
  
  
  
  18. GO  
    19. 
  
  
  
  19. DBCC FREEPROCCACHE --清除缓存，以免下次计算时间  
    20. 
  
  
  
  20. declare @begin datetime  
    21. 
  
  
  
  21. declare @End datetime  
    22. 
  
  
  
  22. set @begin=getdate()  
    23. 
  
  
  
  23. exec a 'p05'  
    24. 
  
  
  
  24. set @End=getdate()  
    25. 
  
  
  
  25. select datediff(ms,@begin,@End) as 执行时间(毫秒) 
    26.

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

--题5：要得到从代理商a03处订购了产品p07的顾客的名字

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

--题6：检索由住在Duluth的顾客和住在New York 的代理商组成的所有订货记录的ordno值

 
 
 
    
  
  
  
  1. 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 
    2.

--答案：

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

--疑惑：难道in比exists执行效率高，还是只是该题的问题。
--题7：找出佣金百分率最小的代理商的aid值
select top(1) aid from agents order by [percent] --我能想到的就是排序然后取***个，但是我这样做有问题，因为我求出来的只可能有 一个，而实际情况是可能有相同值的不止一个
--答案：

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

----题8：找出住在Dallas或Boston的顾客拥有相同折扣的所有顾客
--select c1.cname ,c2.cname from customers c1,customers c2 where c1.discnt=c2.discnt and c1.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 --这种题目应谨慎，留意

--题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' )
----这样做虽 然答案正确，但是换位置之后就有错误了
--遇到这种问题的思路是什么样的？

--正确答案：

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

--为什么这里一定要取别名
--取别名除了有方便的好处外，有什么情况是必须用到的吗？

 
 
 
    
  
  
  
  1. select cid from orders where pid='p01' intersect select cid from orders where pid='p07'  
    2.

--注：两个的交集，可以用intersect关键字

--3.4.12 检索没有通过代理商a05订货的所有顾客的名字
select cid,cname from customers where cid not in (select cid from orders where aid='a05')
--这个时候in 不能用exists 代替
----答案：

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

----实际上答案是错的，但是中文解释好像又能够解释通，为什么呢？

--3.4.15检索订购了产品p01的顾客所在的city

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

--3.5.1 建立一个包含了顾客所在的或者代理商所在的或者两者皆在的城市的名单

 
 
 
    
  
  
  
  1. select distinct city from agents union (select city from customers) 
    2.

--3.5.2 求出通过住在New York的所有代理商订了货的顾客的cid值

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

--3.5.3 求出住在New York 或Duluth 并订购了价格超过一美元的所有产品的代理商的aid值

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

--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的重要性。

--答案：

 
 
 
    
  
  
  
  1. 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   
    2. 
  
  
  
  2. not exists (select * from orders x where x.pid=p.pid and x.aid=y.aid)) 
    3.

--3.5.5 找出具有以下性质的顾客的cid 值：如果顾客c006订购了某种产品，那要检索的顾客也订购了该产品

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

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

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

--3.5.6 找出被所有住在Duluth的顾客订购的产品的pid值

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

--同理：肯定是错的，对待这种要包含的问题该如何写sql语句
--答案：

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

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

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

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

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

本文标题：SQLServer实践性练习之子查询实例
网页链接：http://www.36103.cn/qtweb/news17/11867.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

网站建设知识

分类信息网站

保温橡塑管　　 　兴久牧业　　 　成都网站建设报价　　 　h5页面制作　　 　水泥基自流平　　 　云服务器　　 　成都网站制作　　 　香港云虚拟主机　　 　成都店面装修公司　　 　高端网站设计　　 　内江广告　　 　成都定制网页设计　　 　专业网站设计　　 　bgp多线机房托管　　 　成都网络营销　　 　做网站的公司　　 　托管服务器　　 　营销网站建设　　 　成都网络公司　　 　崇州网站建设