SQL Server實踐性練習之高級SQL查詢
上次我們介紹了：SQL Server實踐性練習之子查詢實例，本文我們主要介紹一些SQL Server實踐性練習的一些高級SQL查詢的實例，接下來就讓我們來一起了解一下這部分內容。
--3.6.2 檢索沒有通過代理商a05訂貨的所有顧客的名字
- select cname from customers except
- (select cname from customers,orders where customers.cid=orders.cid and orders.aid='a05')
--這時except是關鍵
---3.6.3 檢索對同一產品至少訂購了兩次的所有顧客的名字
- 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 檢索至少訂購了一件價格低于￥0.50 的商品的所有顧客的姓名
--答案:我沒做出來,下面這種方法運行沒通過
select distinct cname from (orders join products using(pid)) join customers using(cid) where price<0.50
--法2:將3個表直接連接起來就可以了
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 求出產品p03的訂購總量
select pid,count(pid) as 訂購總量 from orders where pid='p03' group by pid --錯誤的，沒理解題意
--答案：
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)
--實際上那條空值的記錄沒有選進來
--3.7.6 找出至少被兩個顧客訂購的所有產品（可以推廣到多于兩個顧客的情況）
select pid from orders group by pid having count(cid)>=2
--我的思路是 select pid from orders
--select pid,count(cid) as 產品被幾個顧客訂購 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)建一個計算每樣產品被每個代理商訂購的總量的查詢
select aid,pid,sum(qty) as 每個代理商訂購的總量 from orders group by aid,pid
3、執(zhí)行效率的分析
--題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';
--那么我們來看一下三種情況的執(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的效率極低。
關于SQL Server數(shù)據(jù)庫實踐性練習之高級SQL查詢的實例介紹就到這里了，希望本次的介紹能夠對您有所幫助。
SQL Server數(shù)據(jù)庫實踐性練習的相關文章：
SQL Server實踐性練習之創(chuàng)建庫表及條件查詢
【編輯推薦】
