SQL Server實(shí)踐性練習(xí)之高級(jí)SQL查詢
上次我們介紹了：SQL Server實(shí)踐性練習(xí)之子查詢實(shí)例，本文我們主要介紹一些SQL Server實(shí)踐性練習(xí)的一些高級(jí)SQL查詢的實(shí)例，接下來(lái)就讓我們來(lái)一起了解一下這部分內(nèi)容。
--3.6.2 檢索沒有通過(guò)代理商a05訂貨的所有顧客的名字
- select cname from customers except
- (select cname from customers,orders where customers.cid=orders.cid and orders.aid='a05')
--這時(shí)except是關(guān)鍵
---3.6.3 檢索對(duì)同一產(chǎn)品至少訂購(gòu)了兩次的所有顧客的名字
- select cname from customers where cid in
- (select cid from orders group by cid,pid having count(pid)>=2)
--答案:
- select distinct cname from (select o.cid as spcid from orders o,orders x where o.cid=x.cid
- and o.pid=x.pid and o.ordno<> x.ordno)y, customers c where y.spcid=c.cid;
--3.6.4 檢索至少訂購(gòu)了一件價(jià)格低于￥0.50 的商品的所有顧客的姓名
--答案:我沒做出來(lái),下面這種方法運(yùn)行沒通過(guò)
select distinct cname from (orders join products using(pid)) join customers using(cid) where price<0.50
--法2:將3個(gè)表直接連接起來(lái)就可以了
select distinct cname from (orders o join products p on o.pid=p.pid) join customers c on o.cid=c.cid where p.price<0.5
--3.7.1 求出所有訂貨交易的總金額
select sum(dollars) as totaldollars from orders;
--3.7.2 求出產(chǎn)品p03的訂購(gòu)總量
select pid,count(pid) as 訂購(gòu)總量 from orders where pid='p03' group by pid --錯(cuò)誤的，沒理解題意
--答案：
select sum(qty) as total from orders where pid='p03'
--3.7.3 求出顧客總數(shù)的查詢
select count(*) as 顧客總數(shù) from customers
--3.7.4 求出有顧客居住的城市的數(shù)目
select count(distinct city) as 有顧客居住的城市數(shù)目 from customers
--3.7.5 列出折扣值小于***折扣值的所有顧客的cid值
- select cid,cname,discnt from customers where discnt<
- (select max(discnt) from customers)
--實(shí)際上那條空值的記錄沒有選進(jìn)來(lái)
--3.7.6 找出至少被兩個(gè)顧客訂購(gòu)的所有產(chǎn)品（可以推廣到多于兩個(gè)顧客的情況）
select pid from orders group by pid having count(cid)>=2
--我的思路是 select pid from orders
--select pid,count(cid) as 產(chǎn)品被幾個(gè)顧客訂購(gòu) from orders group by pid having count(cid)>=2
--答案如下：
select p.pid from products p where 2<=(select count(distinct cid) from orders where pid=p.pid)
--3.7.7
insert into customers (cid,cname,city)
values ('c009','Windix','Dallas');
select * from customers where discnt<=10 or discnt>10
--顯然,沒有查出所有記錄
--使用特殊謂詞is null
select * from customers where discnt is null or discnt<=10 or discnt>10
--3.8 SQL中行的分組
--3.8.1 創(chuàng)建一個(gè)計(jì)算每樣產(chǎn)品被每個(gè)代理商訂購(gòu)的總量的查詢
select aid,pid,sum(qty) as 每個(gè)代理商訂購(gòu)的總量 from orders group by aid,pid
3、執(zhí)行效率的分析
--題4：找出訂購(gòu)了產(chǎn)品p05的顧客的名字
select cname from customers where cid in (select cid from orders where pid='p05')
--答案用最直接的SQL語(yǔ)句來(lái)解決該查詢問(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í)間(毫秒)
--由此可見，一般情況下這種題目能直接寫的就直接用連接的方法，用in的效率極低。
關(guān)于SQL Server數(shù)據(jù)庫(kù)實(shí)踐性練習(xí)之高級(jí)SQL查詢的實(shí)例介紹就到這里了，希望本次的介紹能夠?qū)δ兴鶐椭?/p>
SQL Server數(shù)據(jù)庫(kù)實(shí)踐性練習(xí)的相關(guān)文章：
SQL Server實(shí)踐性練習(xí)之子查詢實(shí)例
SQL Server實(shí)踐性練習(xí)之創(chuàng)建庫(kù)表及條件查詢
【編輯推薦】
分享到微信 
分享到微博
2012-08-29 09:29:28
