SQLServer學習筆記系列5

一.寫在前面的話 轉眼又是一年清明節，話說清明時節雨紛紛，武漢的天氣伴隨著這個清明節下了一場暴雨，整個城市如海一樣，朋友圈滲透著清明節武漢看海的節奏。今年又沒有回老家祭祖，但是心里依然是懷念著那些親人，雖說他們已離我們遠去，然而那些血濃于水

一.寫在前面的話

轉眼又是一年清明節，話說“清明時節雨紛紛”，武漢的天氣伴隨著這個清明節下了一場暴雨，整個城市如海一樣，朋友圈滲透著清明節武漢看海的節奏。今年又沒有回老家祭祖，但是心里依然是懷念著那些親人，雖說他們已離我們遠去，然而那些血濃于水的親情是一輩子無法忘記的，在心里深深的想念他們。生活繼續，激情永恒！時刻保持著奮斗的節奏，為那些愛我們的和我愛的人，好好活著，做一個斗士，讓我們都能夠獲得幸福！繼續我們的學習吧！在這里首先分享海子的一首詩：

面對大河我無限慚愧，

我年華虛度，空有一身疲倦，

和所有以夢為馬的詩人一樣，

歲月易逝，一點不剩。

------ 摘自《海子的詩》

二.查詢缺少值的查詢

在這里我們加入要查詢2008年每一天的訂單有多少？首先我們可以查詢下訂單表的訂單日期在2008年的所有訂單信息。

1 select distinct orderdate,count(*) as N'每日訂單量' from sales.orders
2 where orderdate between '20080101' and '20081231'
3 group by orderdate

查詢結果如圖：

從上面可以看出來，每天的訂單的數量根據orderdate分組以后統計出來啦，但是我們發現有的日期是不存在的，比如2008-01-01、2008-01-02....卻沒有發現2008-01-03日期的訂單數量，加入我們要求看到每天的訂單了？（這種要求大多數來源于財務報表的統計），這就要求我們進行表構造，我們可以構造一個包含2008年的每一年日期，然后進行表關不就得出來每一天的都包含的訂單嘛。說著我就開始做吧，先開始構造一個包含2008年每一天的表。

1 create table nums
2 (
3 n int
4 );
5 
6 select * from nums;

創建一個nums空表，用來保存連續的日期。接著就可以往表里面插入一些數據。

1 declare @i int;
2 set @i=0;
3 while @i<400
4 begin
5 set @i=@i+1;
6 insert into nums(n) values(@i);
7 end

可以看到表里面插入和1到400有序的數字：

接著我們就可以構造連續日期了，日期的相加前面已經學習過dateadd（），如果想一起學習一下，可以看一下前面的筆記：

sqlserver學習筆記1：http://www.cnblogs.com/liupeng61624/p/4354983.html

sqlserver學習筆記2：http://www.cnblogs.com/liupeng61624/p/4367580.html

sqlserver學習筆記3：http://www.cnblogs.com/liupeng61624/p/4375135.html

sqlserver學習筆記4：http://www.cnblogs.com/liupeng61624/p/4388959.html

繼續說日期的相加，在這里我們通過日期相加，就可以構造2008年的每一天：

1 select dateadd(day,n,'20071231')
2 from nums;

構造的日期結果如圖：

日期構造完以后，那么我們就可以利用這個結果集跟訂單表Sales.orders進行一個連接。

1 select dateadd(day,f.n,'20071231'),count(orderid) as N'每日訂單數量'
2 from nums f left join sales.orders m on
3 dateadd(day,f.n,'20071231')= m.orderdate
4 group by dateadd(day,f.n,'20071231')
5 order by dateadd(day,f.n,'20071231')

結果如圖所示：

（2）子查詢，即查詢結果可以作為一個查詢條件。

例如：我們要查詢雇員表（Hr.employees）里面年齡最小的雇員信息。sql語句可以這樣寫：

1 select max(birthdate) as N'生日'
2 from hr.employees

在這里我們知道可以用聚合函數max進行查詢，但是加入我們還要查詢出年齡最小的名字，即lastname，sql語句如下，可以發現報錯，因為max聚合函數，是對一組結果進行處理，而lastname并不包含在聚合函數中，故報錯。

那么在這里我們就要用到子查詢來處理，可以講年齡最小的結果作為查詢結果來進一步查詢。

1 select birthdate,lastname
2 from hr.employees
3 where birthdate=
4 (
5 select max(birthdate) 
6 from hr.employees
7 )

查詢結果如圖所示：

繼續子查詢，加入我們要將下訂單最貴的那個客戶找出來，給頒發一個Svip級別榮譽，并且找出他所在的國家已經他個人的一些基本信息。

根據上面子查詢，我們可以這樣寫我們的sql，首在這里視圖Sales.OrderValues里面存儲的是訂單的一些價格信息。故我們對這張視圖進行操作。

1. 首先找出訂單最貴的信息

1 
2 select max(val) as N'最貴訂單'
3 from Sales.OrderValues

2. 然后找出最貴訂單的顧客ID是多少

1 select custid from Sales.OrderValues
2 where val=(
3 select max(val) as N'最貴訂單'
4 from Sales.OrderValues
5 )

3. 接著我們就可以在顧客表里面找出ID等于查詢來的這個ID，同時查找出所在國家。

 1 select custid,contactname,country
 2 from sales.customers where custid=
 3 (
 4 select custid from Sales.OrderValues
 5 where val=
 6 (
 7 select max(val) as N'最貴訂單'
 8 from Sales.OrderValues
 9 )
10 )

結果如圖所示：

三.相關子查詢，即查詢的嵌套另一個查詢，其中有涉及到相互關聯的條件。

例如：我們要查詢每個顧客下的訂單數量，前面我們已經學習過，有兩種方法都可以實現:

1.用group......by分組

1 select custid, count(*) as N'訂單數量' from sales.orders
2 group by custid order by custid

2.利用count.....over

1 select distinct custid,count(*) over (partition by custid) as N'訂單數量'
2 from sales.orders

第三種方式我們就用相關子查詢來解決，可以這樣理解：就是我們沒查一位顧客的訂單數量就是去訂單表里面顧客Id相同的都取出來，然后利用聚合函數求和。顧客ID我們可以從顧客表里面取出來，然后這個ID就等于訂單表里面的ID。所以根據分析我們寫sql如下：

1 select n.custid,n.contactname,
2 (
3 select count(*) 
4 from sales.orders m 
5 where m.custid=n.custid
6 ) as N'訂單數量'
7 from sales.customers n

其結果如圖所示：

這樣也可以把顧客下的訂單數量算出來，這里就是利用到了外層查詢跟內層查詢條件作為比對求和。也就是我們說的相關子查詢。

四.多值子查詢

例如：我們要查詢存在顧客但卻沒有供應商的國家，即這個國家中有顧客，沒有供應商公司。

一般情況下：我們會采用常用的sql寫法：

1 
2 select distinct m.country from sales.customers m
3 where m.country not in
4 (
5 select n.country from production.suppliers n
6 )

結果如圖所示：

既然有了not.....in寫法，當然存在exists的寫法，同樣可以實現要求，exists對于結果集若存在則返回true，不存在返回false。我們可以這樣理解：外層查詢將country傳遞到內層查詢，看看存不存在其中，其中內存查詢包含多個結果，所以就叫做多值子查詢。所以sql語句可以這樣寫：

1 select distinct m.country from sales.customers m
2 where not exists 
3 (
4 select n.country from production.suppliers n
5 where n.country= m.country 
6 )

結果如圖所示：

可以看到其結果跟not.....in查出來的結果一樣，滿足條件。

五.復雜子查詢

（1）例如：假如我們要查詢所有訂單當前訂單的前一個訂單和后一個訂單信息，這里我們先分析：

1.首先我們先可以查詢出所有的訂單。

1 select distinct custid
2 from sales.orders

2.然后查詢比當前訂單Id小于的訂單，同時這個訂單是小于當前訂單中最大的那個訂單（即緊挨著的訂單）。

1 select distinct 
2 (
3 select max(custid) from 
4 sales.orders m where m.custid< n.custid
5 ) as N'前一個訂單',n.custid as N'當前訂單'
6 
7 from sales.orders n

3.同理，可以查出大于當前訂單的那個緊挨著的那個訂單。

 1 select distinct 
 2 (
 3 select max(custid) from 
 4 sales.orders m where m.custid< n.custid
 5 ) as N'前一個訂單',n.custid as N'當前訂單',
 6 (
 7 select min(custid) from 
 8 sales.orders p where p.custid> n.custid
 9 ) as N'后一個訂單'
10 from sales.orders n

其結果如圖所示：

（2）累計聚合

累計聚合在財務統計中，經常用到，比如2007年賣出多少，2008年賣出多少，那么2008年累計賣出就是2007年加上2008年賣出的總和，即累計聚合。

在這里我們有視圖Sales.OrderTotalsByYear，其中統計的是每一年的訂單總量。

1 select * from Sales.OrderTotalsByYear

我們可以看到2007年有25489張訂單，2008年有16247張訂單，2006年有9581張訂單。加入我們要求每年累計賣了多少訂單，就要用到累計聚合。

1 select n.orderyear,
2 (
3 select sum(qty)
4 from Sales.OrderTotalsByYear m
5 where m.orderyear<=n.orderyear
6 ) as N'累計訂單數量'
7 from Sales.OrderTotalsByYear n
8 order by n.orderyear;

結果如圖所示：

今天就學習到這，下次接著學習CTE，有了CTE會讓我們的查詢更加爽，特別是在用到遞歸的時候。

希望各位大牛給出指導，不當之處虛心接受學習！謝謝！

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com