SQLServer學習筆記系列7

標籤：

 

一.寫在前面的話

轉眼又是周一，回想雙休的日子，短暫而幸福，在陽光明媚的下午，可以自己做自己想做的任何事，愜意舒適，或讀書，或運動，或音樂，當我們靜下心來慢慢感受這些的時候，會突然發覺，原來生活是這麼的幸福！有所求，有所感，就夠啦！簡簡單單的生活其實就是最奢華的享受！忘記不開心的事，做自己生活的主導，擺脫煩惱，希望園子的朋友們，都能有一個好心情，不順心的時候，出去走走，讓情緒行走無邊，放空自己！相信美好的事情終將發生!

二.視圖

視圖可以看作定義在SQL Server上的虛擬表.視圖包含查詢的一組結果集.常規視圖本身並不儲存實際的資料，而僅僅儲存一個Select語句和所涉及表的metadata.利用視圖，可以根據我們的需要，將多個表的資料進行組合，而且視圖一旦建立，就一直存在，可以迴圈使用。

例如：假如我們要尋找美國顧客的相關資訊，那麼就可以建立一個視圖，每次只要查詢美國顧客資訊，只要根據視圖名稱查詢就可以啦！同理，視圖也需要先定義再查詢。sql如下：

 

1 CREATE VIEW USA_cusomers2 AS 3 (4    SELECT * FROM 5    sales.customers6    WHERE country=‘USA‘7 )

定義完成以後，執行sql，那麼命令就建立完成，然後就可以利用sql查詢語句來查詢美國的顧客資訊。

1 SELECT custid,country FROM dbo.USA_cusomers;

執行結果：

同時我們在sql的物件總管中——》視圖中可以看到，已經添加了名稱為dbo.USA_cusomers的視圖，那麼可以迴圈利用這個視圖進行查詢。

如果想刪除這個視圖的話，可以利用sql語句drop進行操作：

1 DROP VIEW dbo.USA_cusomers;

三.集合運算

集合預算主用用於對查詢的結果進行操作。合集（Union）、交集（Intersect）、差集（Except），跟數學中的集合運算一樣。

（1）合集，將所查詢的兩個或者多個結果集進行合并進行展示。

例如：我們要查詢顧客表（sales.customers）和僱員表（hr.employees）裡面所有的國家資訊，那麼就需要先查詢出顧客表裡面的所有國家，然後查詢僱員表裡面的所有國家，將兩個結果集進行合并。sql如下：

1 SELECT country2 FROM Sales.Customers3 UNION ALL4 SELECT country5 FROM hr.Employees;

注意此處用的是union all，那麼假如使用union結果又是什麼了？

1 SELECT country2 FROM Sales.Customers3 UNION 4 SELECT country5 FROM hr.Employees;

可以看到使用union all與union之間進行合集以後，結果集是不同的，其實這就說明了一點：

union all 不去重，包含所有的結果。

union 去重，顯示的是去重以後的結果集。所以上述結果集顯示21條，是去重後的結果。

（2）交集，將所查詢的兩個或者多個結果集進行交集展示。其中包含兩個結果集中相同的部分。

同樣我們將顧客表（sales.customers）和僱員表（hr.employees）裡面所有的國家資訊進行交集處理，看哪些國家既有顧客也有僱員。sql如下：

1 SELECT country2 FROM Sales.Customers3 intersect 4 SELECT country5 FROM hr.Employees;

其中需要注意的是：intersect求交集，也是去重後的結果。

（3）差集，將所查詢的兩個或者多個結果集進行差集合并，找出其中一個集合在另一個集合中不存在的結果集。

例如：我們找出僱員表（hr.employees）裡面的員工所在國家在顧客表（sales.customers）裡面不存在的部分，也就是找出有顧客沒有僱員的國家有哪些。sql如下：

1 SELECT country2 FROM Sales.Customers3 EXCEPT 4 SELECT country5 FROM hr.Employees;

四.透視（pivot）

所謂的透視，也就是表的轉置，在資料庫操作中，有些時候我們遇到需要實現“行轉列”的需求，統計每一階段或者每一季度，每一星期的數量，可能資料庫中存的資料格式是一行一行的，那麼我們需要用一列展示具體的統計情況，這時候就需要用到透視（pivot）。

在這裡我們首先建立一張表dbo.orders,同時往表裡面插入一些資料。

 1 IF OBJECT_ID(‘dbo.orders‘,‘U‘) IS NOT NULL 2 DROP TABLE dbo.orders; 3 CREATE TABLE dbo.orders 4 ( 5    orderid int NOT NULL  PRIMARY KEY, 6    empid int NOT NULL, 7    custid int NOT NULL, 8    orderdate datetime, 9    qty int 10 );11 12 INSERT INTO dbo.orders(orderid,empid,custid,orderdate,qty)13 VALUES (30001,3,1,‘20070802‘,10),14        (30002,2,4,‘20070601‘,20),15         (10001,4,5,‘20070802‘,30),16         (20001,5,2,‘20070802‘,40),17         (40001,3,2,‘20070802‘,50),18         (30006,5,6,‘20070802‘,50),19         (30008,4,8,‘20070802‘,60),20         (60001,6,1,‘20070802‘,70)

1 SELECT * FROM dbo.orders

查詢表資料有：

現在假如有一個需求，要求出每位顧客所消費的金額，根據常規想法，我們可以根據顧客id進行分組，然後用彙總函式sum求和，sql語句如下：

1 SELECT empid,SUM(qty) AS N‘顧客消費金額‘2 FROM dbo.orders3 GROUP BY empid;

但是我們現在想將顧客消費金額變成一列，也就是行轉列，該怎麼考慮了，在這裡我們先用傳統的方式，用到case when，然後根據case when條件進行一次求和。sql語句如下：

1 SELECT empid,2 SUM(CASE when empid=2 THEN qty end) AS N‘2號顧客消費金額‘,3 SUM(CASE when empid=3THEN qty end) AS N‘3號顧客消費金額‘,4 SUM(CASE when empid=4 THEN qty end) AS N‘4號顧客消費金額‘,5 SUM(CASE when empid=5 THEN qty end) AS N‘5號顧客消費金額‘,6 SUM(CASE when empid=6 THEN qty end) AS N‘6號顧客消費金額‘7 FROM dbo.orders8 GROUP BY empid;

其執行結果

從執行結果圖可以看出，已經將顧客ID轉換到一行顯示，每位顧客的消費在列中都可以看到。但是這裡我們採取新的一種方式，顯得更好用，那就是pivot方式。只不過pivot幫我們做了很多的轉換工作而已，這裡我們只關注pivot如何用。對於上面的需求，我們可以這樣使用pivot：

 1 SELECT empid,[1],[2],[4],[6],[8] 2 FROM  3 (   4     --只返回pivot中用到的列 5    SELECT empid,qty,custid 6    FROM dbo.orders 7 ) AS t 8 PIVOT ( 9      SUM(t.qty) FOR t.custid IN ([1],[2],[4],[6],[8])--做列名稱10 ) AS P

 

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

SQLServer學習筆記系列7