你真的會玩SQL嗎？表表達式，排名函數

你真的會玩SQL嗎？系列目錄

你真的會玩SQL嗎？之邏輯查詢處理階段

你真的會玩SQL嗎？和平大使内連接配接、外連接配接

你真的會玩SQL嗎？三範式、資料完整性

你真的會玩SQL嗎？查詢指定節點及其所有父節點的方法

你真的會玩SQL嗎？讓人暈頭轉向的三值邏輯

你真的會玩SQL嗎？EXISTS和IN之間的差別

你真的會玩SQL嗎？無處不在的子查詢

你真的會玩SQL嗎？Case也瘋狂

你真的會玩SQL嗎？表表達式，排名函數

你真的會玩SQL嗎？簡單的資料修改

你真的會玩SQL嗎？你所不知道的資料聚合

你真的會玩SQL嗎？透視轉換的藝術

你真的會玩SQL嗎？冷落的Top和Apply

你真的會玩SQL嗎？實用函數方法彙總

你真的會玩SQL嗎？玩爆你的資料報表之存儲過程編寫（上）

你真的會玩SQL嗎？玩爆你的資料報表之存儲過程編寫（下）

這次講的有些可能是經常用但不會注意到，是以來統一總結一下用法。

我們往往需要臨時存儲某些結果集。除了用臨時表和表變量，還可以使用公用表表達式的方法。

表表達式

1. 期待單個值的地方可以使用标量子查詢
2. 期待多個值的地方可以使用多值子查詢
3. 在期待出現表的地方可用表值子查詢或表表達式

1.派生表

是從查詢表達式派生出虛拟結果表的表表達式，派生表的存在範圍隻是外部查詢。

使用形式：from 派生表 as 派生表列名

規則：

1. 所有列必須有名稱
2. 列名必須唯一
3. 不允許使用order by（除非指定了top）

不同于标量和多值子查詢，派生表不能是相關的，它必須是獨立的。

2.公用表表達式（CTE）

非遞歸公用表表達式（CTE）是查詢結果僅僅一次性傳回一個結果集用于外部查詢調用。

WITH CTE_Test
　　AS
　　(
    　　SELECT * FROM Person_1
　　)
　　SELECT * FROM CTE_Test AS a　　--第一次引用
　　INNER JOIN  CTE_Test AS b　　　 --第二次引用
　　ON a.Id = b.Id
　　ORDER BY a.Id DESC
--SELECT * FROM CTE_Test 再查詢一次會報錯

遞歸公用表達式

來引用他人的一個示例：

先建一張表欄目表如下，欄目Id，欄目名稱，欄目的父欄目

現在使用CTE查詢其每個欄目是第幾層欄目的代碼如下：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本語句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --遞歸語句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c 
    INNER JOIN COL_CTE AS ce 　　--遞歸調用
    ON c.ParentId = ce.Id
)

SELECT * FROM COL_CTE

結果：

0表示頂級欄目。1就是1級欄目

排名函數

四個排名函數：

　　1.row_number

　　2.rank

　　3.dense_rank

　　4.ntile　

排名函數order by子句是必需的。我們這裡不講定義，直接講執行個體用法。

利用row_number生成連續行号

SELECT  empid ,
        qty ,
        ROW_NUMBER() OVER ( ORDER BY qty ) AS rownum
FROM    sales
ORDER BY qty

小的分組範圍内排序，通過PARTITION BY選項來重新排序，給資料分區或者資料區域唯一的遞增序号

如：LastName以‘A’開頭的作為第一組，在這個組内進行排序。以‘B’開頭的作為第二組，在這個組内排序。以‘C’開頭的作為第三組，在這個組内進行排序，如此等等

select
ROW_NUMBER() over(PARTITION by substring(LastName,1,1) order by LastName) as RowNum,
FirstName+' '+ LastName as FullName
from HumanResources.vEmployee

結果

假設LastName以‘A’開頭的是男子組，這個組有共有三個人，Kim Abercrombie是冠軍，Jay Adams是亞軍，Nancy Anderson是季軍。假設LastName以‘B’開頭的是女子組，這個組隻有一個人Bryan Baker，無論如何她都是冠軍。等等如此類推。這樣一眼就能看出他們的小組名次了。

RANK

果有同時撞線的情況發生應該怎麼計名次呢？例如A第一個撞線，B和C同時第二個撞線，D第三個撞線，如果我們想把D的名次計為第4名應該怎麼處理呢？就是說不計順序名次，隻計人數。這時就可以使用RANK函數了。

在order by子句中定義的列上，如果傳回一行資料與另一行具有相同的值，rank函數将給這些行賦予相同的排名數值。在排名的過程中，保持一個内部計數值，當值有所改變時，排名序号将有一個跳躍。

SELECT  ROW_NUMBER() OVER ( ORDER BY Department ) AS RowNum ,
        RANK() OVER ( ORDER BY Department ) AS Ranking ,
        FirstName + ' ' + LastName AS FullName ,
        Department
FROM    HumanResources.vEmployeeDepartment
ORDER BY RowNum

rank()函數右面也要跟上一個over子句。為了看到效果我們以Department作為排序字段，可以看到RowNum作為升序連續排名，Ranking作為計同排名，當Department的值相同時，Ranking中的值保持不變，當Ranking中的值發生變化時，Ranking列中的值将跳躍到正确的排名數值。來看結果：

從這個結果中我們可以說這次馬拉松賽跑的排名是：Tengiz Kharatishvili，Zainal Arifin，Sean Chai，Karen Berge，Chris Norred并列第1，Michael Sullivan，Sharon Salavaria，Roberto Tamburello，Gail Erickson，Jossef Goldberg并列第6，如此等等。

DENSE_RANK

A第一個撞線，B和C同時第二個撞線，D第三個撞線，如果我們想把B和C的名次計位第2名，D的名次計為第3名應該怎麼處理呢？就是說考慮并列名次。這裡使用DENSE_RANK函數

SELECT  ROW_NUMBER() OVER ( ORDER BY Department ) AS RowNum ,
        DENSE_RANK() OVER ( ORDER BY Department ) AS Ranking ,
        FirstName + ' ' + LastName AS FullName ,
        Department
FROM    HumanResources.vEmployeeDepartment
ORDER BY RowNum

按照這個結果，我們可以說這次馬拉松賽跑的排名是：Tengiz Kharatishvili，Zainal Arifin，Sean Chai，Karen Berge，Chris Norred并列第1，Michael Sullivan，Sharon Salavaria，Roberto Tamburello，Gail Erickson，Jossef Goldberg，Terri Duffy并列第2，等等。

NTILE

梭羅是鉛筆的發明者，不過他沒有申請專利。據說他天賦異禀，在父親的鉛筆廠裡面打包鉛筆的時候，從一堆鉛筆裡面抓取一把，每次都能精确地抓到一打12支。他在森林中目測兩顆樹之間的距離，和護林員用卷尺測量的結果相差無幾。現在如果我們想從一張表中抓取多比資料，每一筆都是相同的數目，并且标明第幾組該怎麼辦呢？NTILE函數提供了這個功能。

SELECT  NTILE(30) OVER ( ORDER BY Department ) AS NTiles ,
        FirstName + ' ' + LastName AS FullName ,
        Department
FROM    HumanResources.vEmployeeDepartment

現在我們要抓取30個組的資料，并保證盡可能的保證每組數目相同

這個視圖中共290條資料，290/30=9.7約等于10，是以每組10條資料，如圖每一條資料都有一個組号。這個結果要比索羅精确。

練習

用CTE删除重複資料

建立一個用于測試的表，并在該表裡插入幾條資料(包括重複的資料)

/* Create Table with 7 records- 3 are duplicate records*/
CREATE TABLE DeleteDuplicateTest ( Col1 INT, Col2 INT )
INSERT  INTO DeleteDuplicateTest
        SELECT  1 ,
                1
        UNION ALL
        SELECT  1 ,
                1 --duplicate
        UNION ALL
        SELECT  1 ,
                1 --duplicate
        UNION ALL
        SELECT  1 ,
                2
        UNION ALL
        SELECT  1 ,
                2 --duplicate
        UNION ALL
        SELECT  1 ,
                3
        UNION ALL
        SELECT  1 ,
                4
GO

用CTE删除重複資料4條惟一的記錄

參考SQL

你真的會玩SQL嗎？表表達式，排名函數

WITH    CTE ( COl1, Col2, DuplicateCount )
          AS ( SELECT   COl1 ,
                        Col2 ,
                        ROW_NUMBER() OVER ( PARTITION BY COl1, Col2 ORDER BY Col1 ) AS DuplicateCount
               FROM     DELETEDUPLICATETEST
             )
    DELETE  FROM CTE
    WHERE   DuplicateCount > 1
GO

/*用SQL SERVER 的CTE，它将重新生成一個相同的但附加了一行編号的表。在此方案中，我們有Col1，Col2以及包含這個兩列重複數的列，對于不同的查詢，這個重複數的列可能有不同的值。另一點需要注意的是，一旦CTE被建立，DELETE語句就可以被運作了。這裡我們設定一個條件——當我們讀取到的記錄大于一條（即有重複資料），我們删除除了第一條的所有其他（這裡可能有點繞，簡單的話就是保留一條重複的記錄）*/

View Code

　　以上部分示例總結于：T-SQL中的排名函數

作者：歡醉

公衆号【一個碼農的日常】技術群：319931204 1号群： 437802986 2号群： 340250479

出處：http://zhangs1986.cnblogs.com/

碼雲：https://gitee.com/huanzui

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。

Top

你真的會玩SQL嗎？表表達式，排名函數

繼續閱讀

圍城内外，年輕人的職場2021

你真的會玩SQL嗎？無處不在的子查詢

SQL SERVER排名函數RANK，DENSE_RANK，NTILE，ROW_NUMBERSQL SERVER排名函數RANK，DENSE_RANK，NTILE，ROW_NUMBER

hive：函數：排名函數：Rank（筆記）

666;簡直太詳細了！！！20個Excel函數公式，職場小白直接套用，讓工作事半功倍。1、AVERAGE平均值：對單個單

Hive 之函數 02-常用查詢函數（二）六、視窗函數七、排名函數八、兩個小題