重學SQL Server 筆記(二）

　　對於top關鍵字，你也許很熟悉他的用法，但你知道top查詢返回的是一個表結果還是遊標呢？

　　當使用top的，同一個order by字句既擔當了為top決定行的邏輯優先順序的角色，同時也擔當了它的常規角色（展示資料），只是最終產生的結果由表變成了具有固定順序的遊標。

　  用法有下面三種：1、關鍵字 percent，SQL Server會按照百分比計算應該返回的的滿足條件的行數（向上取整）；

　　用法  select top 1 percent orderid from ordervalues。

　　　　　　　　　  2、選擇需要的前幾行，常用於分頁；

　　用法 select top 10 orderid from ordervalues。

　　　　　　　　3、返回與top n行中最後一行的排序值相同的其他所有行，須添加 with ties選項，如果排序值有多個，則以最後一個排序值為準，這裡為custid。

　　用法　select top(5) with ties orderid,orderdate,custid,empid
　　　　   from orders
              order by empid desc,custid

　　over子句，用於為行定義一個視窗，以便進行特定的運算。我們可以把行的視窗簡單的認為是運算將要操作的一個行的集合。由於over子句可以為彙總函式和次序函數提供一個行的視窗，這些函數也稱為開窗函數。帶有空的圓括弧的over子句會提供所有行進行計算，這裡的所有行並不一定是在from 子句中出現的那些表中的所有行，而是在from、where、group by，以及having處理階段才完成後仍然可以用的那些行。那麼，什麼地方才可以用over子句呢？？ answer就是只有在select和order by處理階段。

　　以下例句中的OrderValues是一個視圖（其中資料可以在我的上一篇博文可以下載）。建立此視圖的語句為

CREATE VIEW OrderValuesASSELECT O.orderid, O.custid, O.empid, O.shipperid, O.orderdate,  CAST(SUM(OD.qty * OD.unitprice * (1 - discount))       AS NUMERIC(12, 2)) AS valFROM Orders AS O  JOIN OrderDetails AS OD    ON O.orderid = OD.orderidGROUP BY O.orderid, O.custid, O.empid, O.shipperid, O.orderdate;

 

　　請看下面Sql語句。其中sum(val) over() as totalValue表示返回當前所有行的val欄位的和，如果我們想得到當前行具有相同custid值得val之和，那麼則可以用partition  by 字句指定求和的關鍵字。

1 select * from OrderValues;2 select orderid,custid,val,3     sum(val) over() as totalvalue,4     sum(val) over(partition by custid) as custtotalvalue5 from OrderValues;

 　　當然，over子句的一個優點是能夠在返回基本行的同時，可以將彙總函式和基本欄共同使用。比如下面Sql語句。

1 select orderid,custid,val,2     100.*val/sum(val) over() as pctall,3     100.*val/sum(val) over(partition by custid) as pctust4 from ordervalues;

　　不僅如此，over子句也支援四種次序函數，row_number(),rank(),dense_rank(),ntile,如下面Sql語句。

1 select orderid,custid,val,2        row_number() over(order by val) as rownum,3        rank() over(order by val) as rank,4        dense_rank() over(order by val) as dense_rank,5        ntile(10) over(order by val) as ntile6 from ordervalues7 order by val

  其中，row_number函數用於為子查詢的結果集中的各行分配遞增的序號，其邏輯順序通過over子句中的order by語句制定，因此，order by子句的欄位的值最好在資料庫中是唯一的。NTILE函數可以把結果中的行關聯到組，NTILE（n)中的n表示制定組的數目，並為每一行分配一個所屬組的編號，因此組的大小是830/10=83,即第一個組裡面有83行資料。當然，次序函數也支援partition by語句。rank()與dense_rank()可以為具有相同邏輯排序值得所有行產生相同的排名。這兩個函數的區別是，rank（）表示之前有多少行具有更低的排序值，而密集次序函數則表示之前有多少個更低的排序值，注意一個是行，一個值。