亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

SQL Server2005雜談(3):四個排名函數(row_n

系統 1797 0
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="ProgId" content="Word.Document"> <meta name="Generator" content="Microsoft Word 11"> <meta name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style> <!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

本文為原創,如需轉載,請注明作者和出處,謝謝!

上一篇: SQL Server2005雜談(2):公用表表達式(CTE)的遞歸調用

排名函數是 SQL Server2005 新加的功能。在 SQL Server2005 中有如下四個排名函數:

1.row_number

2.rank

3.dense_rank

4.ntile
下面分別介紹一下這四個排名函數的功能及用法。在介紹之前假設有一個t_table表,表結構與表中的數據如圖1所示:



圖1
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="ProgId" content="Word.Document"> <meta name="Generator" content="Microsoft Word 11"> <meta name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style> <!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

其中 field1 字段的類型是 int field2 字段的類型是 varchar

一、 row_number

row_number 函數的用途是非常廣泛,這個函數的功能是為查詢出來的每一行記錄生成一個序號。 row_number 函數的用法如下面的 SQL 語句所示:

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> select row_number() over ( order by field1) as row_number, * from t_table

上面的 SQL 語句的查詢結果如圖 2 所示。



圖2
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="ProgId" content="Word.Document"> <meta name="Generator" content="Microsoft Word 11"> <meta name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style> <!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

其中 row_number 列是由 row_number 函數生成的序號列。在使用 row_number 函數是要使用 over 子句選擇對某一列進行排序,然后才能生成序號。

實際上, row_number 函數生成序號的基本原理是先使用 over 子句中的排序語句對記錄進行排序,然后按著這個順序生成序號。 over 子句中的 order by 子句與 SQL 語句中的 order by 子句沒有任何關系,這兩處的 order by 可以完全不同,如下面的 SQL 語句所示:

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> select row_number() over ( order by field2 desc ) as row_number, * from t_table order by field1 desc

上面的 SQL 語句的查詢結果如圖 3 所示。



圖3
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="ProgId" content="Word.Document"> <meta name="Generator" content="Microsoft Word 11"> <meta name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style> <!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]--> 我們可以使用row_number函數來實現查詢表中指定范圍的記錄,一般將其應用到Web應用程序的分頁功能上。下面的SQL語句可以查詢t_table表中第2條和第3條記錄:
<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> with t_rowtable
as
(
select row_number() over ( order by field1) as row_number, * from t_table
)
select * from t_rowtable where row_number > 1 and row_number < 4 order by field1

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]--> 上面的SQL語句的查詢結果如圖4所示。



圖4
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="ProgId" content="Word.Document"> <meta name="Generator" content="Microsoft Word 11"> <meta name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style> <!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]--> 上面的 SQL 語句使用了 CTE ,關于 CTE 的介紹將讀者參閱 《SQL Server2005雜談(1):使用公用表表達式(CTE)簡化嵌套SQL》
另外要注意的是,如果將 row_number 函數用于分頁處理, over 子句中的 order by 與排序記錄的 order by 應相同,否則生成的序號可能不是有續的。
當然,不使用 row_number 函數也可以實現查詢指定范圍的記錄,就是比較麻煩。一般的方法是使用顛倒 Top 來實現,例如,查詢 t_table 表中第 2 條和第 3 條記錄,可以先查出前 3 條記錄,然后將查詢出來的這三條記錄按倒序排序,再取前 2 條記錄,最后再將查出來的這 2 條記錄再按倒序排序,就是最終結果。 SQL 語句如下:
<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> select * from ( select top 2 * from ( select top 3 * from t_table order by field1)a order by field1 desc )b order by field1

上面的 SQL 語句查詢出來的結果如圖 5 所示。



圖5
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

這個查詢結果除了沒有序號列 row_number ,其他的與圖 4 所示的查詢結果完全一樣。

二、 rank

rank 函數考慮到了 over 子句中排序字段值相同的情況,為了更容易說明問題,在 t_table 表中再加一條記錄,如圖 6 所示。



圖6
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]--> 在圖6所示的記錄中后三條記錄的field1字段值是相同的。如果使用rank函數來生成序號,這3條記錄的序號是相同的,而第4條記錄會根據當前的記錄數生成序號,后面的記錄依此類推,也就是說,在這個例子中,第4條記錄的序號是4,而不是2。rank函數的使用方法與row_number函數完全相同,SQL語句如下:

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> select rank() over ( order by field1), * from t_table order by field1

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

上面的 SQL 語句的查詢結果如圖 7 所示。



圖7
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

三、 dense_rank

dense_rank 函數的功能與 rank 函數類似,只是在生成序號時是連續的,而 rank 函數生成的序號有可能不連續。如上面的例子中如果使用 dense_rank 函數,第 4 條記錄的序號應該是 2 ,而不是 4 。如下面的 SQL 語句所示:

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> select dense_rank() over ( order by field1), * from t_table order by field1

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]--> 上面的 SQL 語句的查詢結果如圖 8 所示。



圖8
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="ProgId" content="Word.Document"> <meta name="Generator" content="Microsoft Word 11"> <meta name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style> <!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

讀者可以比較圖 7 和圖 8 所示的查詢結果有什么不同

四、ntile
ntile函數可以對序號進行分組處理。這就相當于將查詢出來的記錄集放到指定長度的數組中,每一個數組元素存放一定數量的記錄。ntile函數為每條記錄生成的序號就是這條記錄所有的數組元素的索引(從1開始)。也可以將每一個分配記錄的數組元素稱為“桶”。ntile函數有一個參數,用來指定桶數。下面的SQL語句使用ntile函數對t_table表進行了裝桶處理:

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> select ntile( 4 ) over ( order by field1) as bucket, * from t_table

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

上面的 SQL 語句的查詢結果如圖 9 所示。



圖9

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="ProgId" content="Word.Document"> <meta name="Generator" content="Microsoft Word 11"> <meta name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style> <!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

由于 t_table 表的記錄總數是 6 ,而上面的 SQL 語句中的 ntile 函數指定了桶數為 4

也許有的讀者會問這么一個問題, SQL Server2005 怎么來決定某一桶應該放多少記錄呢?可能 t_table 表中的記錄數有些少,那么我們假設 t_table 表中有 59 條記錄,而桶數是 5 ,那么每一桶應放多少記錄呢?

實際上通過兩個約定就可以產生一個算法來決定哪一個桶應放多少記錄,這兩個約定如下:

1. 編號小的桶放的記錄不能小于編號大的桶。也就是說,第 1 捅中的記錄數只能大于等于第 2 桶及以后的各桶中的記錄。

2. 所有桶中的記錄要么都相同,要么從某一個記錄較少的桶開始后面所有捅的記錄數都與該桶的記錄數相同。也就是說,如果有個桶,前三桶的記錄數都是 10 ,而第 4 捅的記錄數是 6 ,那么第 5 桶和第 6 桶的記錄數也必須是 6

根據上面的兩個約定,可以得出如下的算法:

<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> --> // mod表示取余,div表示取整
if (記錄總數mod桶數 == 0 )
{
recordCount
= 記錄總數div桶數;
將每桶的記錄數都設為recordCount
}
else
{
recordCount1
= 記錄總數div桶數 + 1 ;
int n = 1 ; // n表示桶中記錄數為recordCount1的最大桶數
m = recordCount1 * n;
while (((記錄總數 - m)mod(桶數 - n)) != 0 )
{
n
++ ;
m
= recordCount1 * n;
}
recordCount2
= (記錄總數 - m)div(桶數 - n);
將前n個桶的記錄數設為recordCount1
將n
+ 1個至后面所有桶的記錄數設為recordCount2
}

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ &#64;font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} &#64;font-face {font-family:""&#64;宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ &#64;page {} &#64;page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->

根據上面的算法,如果記錄總數為 59 ,桶數為 5 ,則前 4 個桶的記錄數都是 12 ,最后一個桶的記錄數是 11

如果記錄總數為 53 ,桶數為 5 ,則前 3 個桶的記錄數為 11 ,后 2 個桶的記錄數為 10

就拿本例來說,記錄總數為 6 ,桶數為 4 ,則會算出 recordCount1 的值為 2 ,在結束 while 循環后,會算出 recordCount2 的值是 1 ,因此,前 2 個桶的記錄是 2 ,后 2 個桶的記錄是 1

下一篇:
SQL Server2005雜談(4):在SQL Server2005中按列連接字符串的三種方法



國內最棒的Google Android技術社區(eoeandroid),歡迎訪問!

《銀河系列原創教程》 發布

《Java Web開發速學寶典》 出版,歡迎定購

SQL Server2005雜談(3):四個排名函數(row_number、rank、dense_rank和ntile)的比較


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 美女一级毛片免费不卡视频 | 在线亚洲精品视频 | 欧美激情一区二区三级高清视频 | 在线中文字幕一区 | 成人国产精品一级毛片视频 | 欧美性白人顶级hd | 日本精品一区二区三区在线观看 | 一级毛片免费网站 | 狠狠操女人 | 四虎四虎1515whh | 国产情侣普通话刺激对白 | 91福利在线观看 | 国产精品久久久久久一区二区 | 亚洲国产精品热久久 | 久久久久久久久久久福利观看 | 美女嗯啊| 一区二区三区中文国产亚洲 | 久在线观看 | 雅虎日本免费一区二区三区 | 亚洲成人伦理 | 亚洲一区二区三区四区五区 | 精品视频在线一区 | 自拍亚洲午夜伦li片影院 | 中文字幕国产在线观看 | 91综合精品网站久久 | 久久精品日日躁精品 | 欧美日韩一区二区高清免费视频 | 黄页成人免费网站 | 亚洲精品99久久久久中文字幕 | 99久久精品免费看国产情侣 | 一级特级欧美a毛片免费 | 国产蜜汁tv福利在线 | 黑人欧美一级毛片 | 国产成人综合一区精品 | 日韩在线观看中文字幕 | 97福利影院 | 拍真实国产伦偷精品 | 久久爱99re| 亚洲精品久久久久久久久久ty | 久热这里只有精品99国产6 | 玖玖在线 |