SQLServer學習筆記系列6

標籤：

 

一.寫在前面的話

時間是我們每個人都特別熟悉的，但是到底它是什麼，用什麼來衡量，可能很多人會愣在那裡。時間可以見證一切，也可以消磨一切，那些過往的點點滴滴可思可憶。回想往年清明節過後，在家鄉的晚上總能聽見陣陣的青蛙叫聲，那是清脆的叫聲，那是家鄉的味道。時間一轉眼，貌似那些日子已離我遠去好久，在城市的喧囂浮華中，找尋不到那種內心的寧靜。感歎時間流逝的同時，懷念過去的點點滴滴。我想在繁華的都市中尋找一種安定的心情來學習，或許是一種不錯的方式。學習才會讓我們認清自己，找回自我，做內心的強者，不驕不躁，積極進取，陽光自信。努力的人最幸運！Just do it！

                                             從今以後，別再過你應該過的人生，去過你想過的人生吧！——梭羅

二.派生表

派生表也就是特殊的子查詢，用在from之後的子查詢，同時這裡注意一點：派生表必須起別名。

例如：加入我們要查詢，顧客資訊，以及每個國家所擁有的顧客數量。那麼我們可以這樣寫：

 

1  2  SELECT * FROM3  (4  SELECT custid,COUNT(*) OVER(PARTITION BY country) AS N‘顧客數量‘5  FROM Sales.Customers6  ) t

括弧裡面的子句就相當於一個派生表，假如我們在這裡不給 COUNT(*) OVER(PARTITION BY country) 起別名，那麼select * 就不知道取出哪一列，所以會報錯。

同時有另一種起別名的方法，也就是在外面其別名，同樣可以達到效果，寫法如下：

1  SELECT * FROM2  (3  SELECT custid,COUNT(*) OVER(PARTITION BY country)4  FROM Sales.Customers5  ) t(custid,顧客數量)

三.CTE(通用資料表運算式)

（1）常見CTE用法

如何使用CTE，在這裡必須先定義,並且命名。定義形式如下：

1  WITH USE_Customers2  AS3  (4    SELECT companyname,country5    FROM Sales.Customers 6    WHERE country=‘USA‘7  )

那麼我們要使用CTE，只要查詢對應的CTE名稱即可。

1  WITH USE_Customers2  AS3  (4    SELECT companyname,country5    FROM Sales.Customers 6    WHERE country=‘USA‘7  )8  9  SELECT * FROM USE_Customers

結果：

但是要注意的一點是：使用CTE時，外部查詢一旦完成，那麼CTE的生命期就結束了，如果需要在查詢這個CTE，此時失效。

同時也可以給CTE每列其別名，其中也存在在運算式內部和外部起別名，看個人喜歡哪種方式。

1  WITH USE_Customers2  AS3  (4    SELECT companyname AS N‘公司名‘,country AS N‘國家名‘5    FROM Sales.Customers 6    WHERE country=‘USA‘7  )8  9  SELECT * FROM USE_Customers

1  WITH USE_Customers(公司名,國家名)2  AS3  (4    SELECT companyname ,country 5    FROM Sales.Customers 6    WHERE country=‘USA‘7  )8  9  SELECT * FROM USE_Customers

其查詢結果都為：

（2）有參數的CTE

使用有參數的CTE可以多次使用，只需要改變參數的傳遞值即可。

例如：查詢每個國家的顧客資訊，國家存在USA，UK........，多個國家，此時我們將國家作為一個變數，類似於C#中的參數。聲明CTE如下：

 1  DECLARE @country NVARCHAR(300); 2  SET @country=‘USA‘; 3  WITH USE_Customers(公司名,國家名) 4  AS 5  ( 6    SELECT companyname ,country  7    FROM Sales.Customers  8    WHERE [email protected] 9  )10  11  SELECT * FROM USE_Customers

假如要查詢UK國家的一些顧客資訊，那麼只需要改變set @country=‘UK’就可以查詢了，不需要改變CTE的結構。

 1  DECLARE @country NVARCHAR(300); 2  SET @country=‘UK‘; 3  WITH USE_Customers(公司名,國家名) 4  AS 5  ( 6    SELECT companyname ,country  7    FROM Sales.Customers  8    WHERE [email protected] 9  )10  11  SELECT * FROM USE_Customers

（3）複雜的CTE使用

【1】在這裡，假如我們要查詢年度訂單數量在10以上的顧客資訊。按照一般的思維，我們可以講訂單按照年度分組，然後用彙總函式count統計每個顧客下的訂單數量，接著找出大於訂單數量大於10的顧客資訊。所以我們可以下sql如下：

查詢結果：

【2】那麼同時可以使用子查詢，即派生表依次來出來問題。

第一步，先查詢訂單的年份，顧客的資訊

1  SELECT  YEAR(orderdate) AS orderyear,custid2  FROM Sales.Orders

第二步，根據年份進行分組，統計每位顧客的訂單數量。第一步查詢出來的結果作為子查詢，即派生表。

1  SELECT orderyear,custid,COUNT(*) AS N‘訂單數量‘2  FROM3  (4      SELECT  YEAR(orderdate) AS orderyear,custid 5      FROM Sales.Orders6  ) AS t17  GROUP BY orderyear,custid

 

第三步，找出訂單數量大於10的顧客資訊。

 1  SELECT orderyear,custid,ordercount 2  FROM 3  ( 4  SELECT orderyear,custid,COUNT(*) AS  ordercount 5  FROM 6          ( 7              SELECT  YEAR(orderdate) AS orderyear,custid  8              FROM Sales.Orders 9          ) AS t110          GROUP BY orderyear,custid11  ) AS t212  WHERE ordercount >10

是不是感覺使用子查詢，一層套一層，不過分析以後，符合我們一般解決問題的思路先做哪一步，再做哪一步。在這裡我們可以使用新學的CTE來解決問題。

【3】使用CTE來解決年度訂單數量在10以上的顧客資訊。

好處就是定義好的CTE，可以直接拿來作為一個派生表使用，那麼我們可以這樣定義CTE，按照上述的分析，分別定義三個CTE，最後查詢出結果：

需要注意的一點事:定義多個CTE之間用逗號（,）隔開即可，不需要使用with重新定。

 1  WITH yearorder01 2  AS 3  ( 4        SELECT YEAR(orderdate) AS orderyear,custid 5        FROM Sales.Orders 6  ), 7  yearorder02 8  AS  9  (10          SELECT orderyear,custid,COUNT(*) AS ordercount11          FROM yearorder0112          GROUP BY orderyear,custid13  ),14  yearorder0315  AS16  (17         SELECT orderyear,custid,ordercount18         FROM yearorder0219         WHERE  ordercount>1020  )21  22  23  SELECT * FROM yearorder03

查詢結果，跟子查詢結果相同：

四.多CTE

多個CTE的使用會讓我們更加方便的進行相互關聯的子查詢。例如：假如我們要查詢年度有多少顧客，測試該怎麼處理了？我們會想到，還是根據年度分組，然後彙總count（custid）求出每年度有多少顧客，其中要注意去重，即相同的custid屬於同一個顧客，所以要寫成count（distinct custid）。sql如下：

1  2  SELECT YEAR(orderdate) AS orderyear,COUNT(DISTINCT custid) AS  custcount3  FROM Sales.Orders4  GROUP BY YEAR(orderdate)

現在加入有一個需求，就是要做一個查詢，求出當年的顧客數量以及前一年的顧客數量，同時算出兩者之間顧客數量隔了多少，我們又該怎麼處理了？要求前一年和當前年的差，那麼就可以想到，是否可以把當前查出來的結果集做一次串連，不就可以得到前一年和當前年的嘛，所以sql如下：

 1  SELECT pre_orderyear,now_orderyear,pre_custcount,now_custcount, 2        (now_custcount-pre_custcount) AS N‘顧客數量差‘ 3  FROM  4  ( 5  SELECT YEAR(orderdate) AS now_orderyear,COUNT(DISTINCT custid) AS now_custcount  6  FROM Sales.Orders 7  GROUP BY YEAR(orderdate) 8  ) AS t1 9  LEFT JOIN 10  (11   SELECT YEAR(orderdate) AS pre_orderyear,COUNT(DISTINCT custid) AS  pre_custcount12  FROM Sales.Orders13  GROUP BY YEAR(orderdate)14  ) AS t215  ON t1.now_orderyear=t2.pre_orderyear+1;

其中就是將結果集做一個自身的串連，但是我們是不是發現這樣寫顯得很冗餘了，作為程式猿的我們就是要寫出盡量簡潔明了的code，Don‘t  repeat yourself ！在這裡我們的CTE就發揮出他的作用，定義的CTE可以在下一步中使用，作為查詢條件。那麼我們看看用CTE如何?？同理，我們使用CTE之前就必須先定義CTE，這裡我們定義一個CTE，查詢出每年的顧客數量，其實跟上面的sql一樣，只不過定義成CTE的形式。

 1  WITH custcount 2  AS 3  ( 4  SELECT YEAR(orderdate) AS orderyear,COUNT(DISTINCT custid) AS  custcount 5  FROM Sales.Orders 6  GROUP BY YEAR(orderdate) 7  ) 8   9  SELECT t1.orderyear AS nowYear,t2.orderyear AS preYear,t1.custcount AS nowcount,t2.custcount AS precount,10        (t1.custcount-t2.custcount) AS N‘顧客數量差‘11  FROM custcount t112  LEFT JOIN  custcount t213  ON t1.orderyear=t2.orderyear+1;

其結果：

其實我們從sql語句可以看出，CTE的簡潔之處，就在於，可以將已定義的CTE重複使用進行串連，相比於子查詢自身的串連，更加簡潔，容易操作，加入需要的計算，我們只需要改變CTE的定義即可。

五.遞迴CTE

遞迴的CTE在遞迴尋找中應用的非常多，其實可以看做一種層次的尋找關係，比如公司一般情況下是分一級部門，二級部門，三級部門.......那麼一級部門下包含多個二級部門，二級部門又包含多個三級部門等等.....,舉例說明：公司內部有上下級的關係，假如我們要尋找某個員工的上級是誰，一般情況下我們可以做一次自身的串連，找出上級是誰。

例如：尋找所有顧客的上級資訊。也就是在HR.employees裡面的mgrid等於員工的empid，那麼找到的那個員工幾位mgrid的上司。sql如下：

1  SELECT t1.empid,t1.mgrid,t1.lastname,t2.empid,t2.lastname2  FROM HR.Employees t1 LEFT JOIN hr.Employees t2 3  ON t1.mgrid =t2.empid

因為為了確保所有的員工資訊都顯示，所以在這裡用到餓左串連，保證左邊表裡面的員工都存在。其結果

這裡再進一步討論，加入我們要查詢某個員工下面所有的下屬資訊，怎麼辦了？比如要查詢2號員工的下屬資訊，我們這可以這樣寫sql：

1  SELECT * FROM 2  hr.Employees WHERE mgrid=2

我們了可以看到2號員工下屬有3號和5號，那麼5號員工可能也有下屬，接著查詢：

1  SELECT * FROM HR.Employees2  WHERE mgrid in3  (SELECT empid FROM 4  hr.Employees WHERE mgrid=25  ) 

我們可以看到，查出的結果中有4，6,7,8,9，那麼4，6,7,8,9下面是否還有下屬了，是不是還需要接著查詢了，這樣查下去就是無止盡的，因為我們不知道有多少層，所以遞迴CTE就是解決這樣的問題的，遞迴CTE可以查詢出所以層級的情況，直至結束。首先遞迴CTE需要定義一個起點，即查詢起點。然後根據定義的CTE自身做遞迴查詢，直至找出所有的節點資訊。如下：

 1 DECLARE @mgrid INT; 2 SET @mgrid=2; 3 WITH Emplist 4 AS 5 ( 6     --此處為起點，執行一次 7     SELECT empid,lastname,mgrid 8     FROM HR.Employees 9     WHERE [email protected]10     UNION ALL11     12     --遞迴開始13     14     SELECT e.empid,e.lastname,e.mgrid15     FROM HR.Employees e INNER JOIN Emplist m16     ON e.mgrid=m.empid 17     18 )19 20 SELECT * FROM Emplist

定義一個變數，接受要查詢的員工id，設為2，即查詢2號員工下面所有的下屬資訊。

關於CTE的運用還有很多，這裡只列出一些基本的用法，如有想法，可以提出，希望一起學習！

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

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

 

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

SQLServer學習筆記系列6