SQLServer學習筆記系列5

標籤：

 

一.寫在前面的話

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

  

                                  面對大河我無限慚愧，

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

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

                                  歲月易逝，一點不剩。

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

 

二.查詢缺少值的查詢

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

1  select distinct orderdate,count(*) as N‘每日訂單量‘ from sales.orders2  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 nums2  (3    n int4  );5  6  select * from nums;

建立一個nums空表，用來儲存連續的日期。接著就可以往表裡面插入一些資料。

1  declare @i int;2  set @i=0;3  while @i<4004  begin5  set @[email protected]+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 on3   dateadd(day,f.n,‘20071231‘)= m.orderdate4   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,lastname2   from hr.employees3   where birthdate=4   (5       select max(birthdate) 6       from hr.employees7   )

查詢結果：

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

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

1. 首先找出訂單最貴的資訊 

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

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

1   select custid from Sales.OrderValues2   where val=(3   select max(val) as N‘最貴訂單‘4   from Sales.OrderValues5   )

      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.orders2   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.custid6   ) as N‘訂單數量‘7   from sales.customers n

其結果：

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

四.多值子查詢

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

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

1   2   select distinct m.country from sales.customers m3   where m.country  not in4   (5       select n.country from production.suppliers n6   )

結果：

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

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

結果：

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

五.複雜子查詢

（1）例如：假如我們要查詢所有訂單當前訂單的前一個訂單和後一個訂單資訊，這裡我們先分析：

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

1   select distinct  custid2   from sales.orders

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

1   select distinct  2   (3      select max(custid) from 4      sales.orders m where m.custid< n.custid5   ) 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  m5    where m.orderyear<=n.orderyear6 ) as N‘累計訂單數量‘7  from  Sales.OrderTotalsByYear n8  order by n.orderyear;

結果：

 

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

 

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

SQLServer學習筆記系列5