<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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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 */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; 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 */ @page {} @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)的比較