作 者：Microsoft
摘 要：行编号或排序是一个典型的过程问题。 其解决方法一般是基于循环函数和临时表，即 SQL Server 循环函数和游标函数。 本文说明如何在执行 SELECT 语句时动态地对行排序。 这是一种很灵活的方法，也可能是唯一可能的解决方法。 它比过程解决方法要快一些。
正 文：
此技术基于“自动联接”(AUTO JOIN) 技术。 所选的关系一般为“大于”。 其目的是当集合与本身比较时，统计有多少次指定数据集合的每个元素实现“大于”关系。 以下示例均基于数据库 pubs。
示例 1
Set 1 是作者。
Set 2 是作者。
关系是“姓名大于”。
通过将“名 + 姓”与其它的“名 + 姓”进行比较，可以避免重复问题。
我们用 count(*) 统计关系实现的次数。
查询：
| select rank=count(*), a1.au_lname, a1.au_fname from authors a1, authors a2 where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname group by a1.au_lname, a1.au_fname order by 1 |
结果：
| Rank Au_Lname Au_Fname ---- -------------- ----------- 1 Bennet Abraham 2 Blotchet-Halls Reginald 3 Carson Cheryl 4 DeFrance Michel 5 del Castillo Innes 6 Dull Ann 7 Greene Morningstar 8 Green Marjorie 9 Gringlesby Burt 10 Hunter Sheryl 11 Karsen Livia 12 Locksley Charlene 13 MacFeather Stearns 14 McBadden Heather 15 O'Leary Michael 16 Panteley Sylvia 17 Ringer Albert 18 Ringer Anne 19 Smith Meander 20 Straight Dean 21 Stringer Dirk 22 White Johnson 23 Yokomoto Akiko (23 row(s) affected) |
示例 2
我们要通过书店售出的书籍数对书店排序。
Set 1 是书店售出的书籍数： select stor_id, qty=sum(qty) from sales group by stor_id。
Set 2 是书店售出的书籍数： select stor_id, qty=sum(qty) from sales group by stor_id。
关系是“书籍数大于”。
为避免重复，我们可以比较 price*qty 而不是实例的 qty。
查询：
| select rank=count(*), s1.stor_id, qty=sum(s1.qty) from (select stor_id, qty=sum(qty) from sales group by stor_id) s1, (select stor_id, qty=sum(qty) from sales group by stor_id) s2 where s1.qty >= s2.qty group by s1.stor_id order by 1 |
结果：
| Rank Stor_Id Qty ---- ------- --- 1 6380 8 2 7896 120 3 8042 240 4 7067 360 5 7066 625 6 7131 780 (6 row(s) affected) |
此示例和以下的某些示例只能在 Microsoft SQL Server 6.5 中运行，因为它们在 FROM 子句使用了派生表。
示例 3
我们要按收入为出版者排序。
Set 1 是出版者的全部销售额：
| select t.pub_id, sales=sum(s.qty*t.price) from sales s, titles t where s.title_id=t.title_id and t.price is not null group by t.pub_id |
Set 2 是出版者的全部销售额：
| select t.pub_id, sales=sum(s.qty*t.price) from sales s, titles t where s.title_id=t.title_id and t.price is not null group by t.pub_id |
关系是“收入大于”。
查询：
| select rank=count(*), s1.pub_id, sales=sum(s1.sales) from (select t.pub_id, sales=sum(s.qty*t.price) from sales s, titles t where s.title_id=t.title_id and t.price is not null group by t.pub_id) s1, (select t.pub_id, sales=sum(s.qty*t.price) from sales s, titles t where s.title_id=t.title_id and t.price is not null group by t.pub_id) s2 where s1.sales>= s2.sales group by s1.pub_id order by 1 |
结果：
| Rank Pub_Id Sales ---- ------ -------- 1 0736 1,961.85 2 0877 4,256.20 3 1389 7,760.85 (3 row(s) affected) |
缺点
因为有“交叉联接”(CROSS JOIN)，所以该方法不适用于处理大量行。 它适用于几十或几百行。 对于大型表，一定要使用索引以避免进行大范围的搜索。
它不能正常处理重复值。 换句话说，当比较重复值时，不连续的行编号会出现。 如果不希望发生这种现象，可以在电子表格中插入结果时隐藏排序列，相反使用电子表格编号。
例如：
| select rank=count(*), s1.title_id, qty=sum(s1.qty) from (select title_id, qty=sum(qty) from sales group by title_id) s1, (select title_id, qty=sum(qty) from sales group by title_id) s2 where s1.qty >= s2.qty group by s1.title_id order by 1 |
结果：
| Rank Title_Id Qty ---- -------- ---- 1 MC2222 10 4 BU1032 60 4 BU7832 60 4 PS3333 60 7 PS1372 140 7 TC4203 140 7 TC7777 140 10 BU1111 250 10 PS2106 250 10 PS7777 250 11 PC1035 330 12 BU2075 420 14 MC3021 560 14 TC3218 560 15 PC8888 750 16 PS2091 1728 (16 row(s) affected) |
优点
这些查询可以用于视图和结果格式设置中。 排序较低的数据可以向右移动。
示例 1：
| CREATE VIEW v_pub_rank AS select rank=count(*), s1.title_id, qty=sum(s1.qty) from (select title_id, qty=sum(qty) from sales group by title_id) s1, (select title_id, qty=sum(qty) from sales group by title_id) s2 where s1.qty >= s2.qty group by s1.title_id |
查询：
| select publisher=convert(varchar(20),replicate (' ', power(2,rank)) + pub_id + replicate(' ', 15-power(2,rank))+': '), earnings=qty from v_pub_rank |
结果：
| Publisher Earnings ------------- -------- 0736 : 1,961.85 0877 : 4,256.20 1389 : 7,760.85 |
示例 2：
| CREATE VIEW v_title_rank AS select rank=count(*), s1.title_id, qty=sum(s1.qty) from (select title_id, qty=sum(qty) from sales group by title_id) s1, (select title_id, qty=sum(qty) from sales group by title_id) s2 where s1.qty >= s2.qty group by s1.title_id |
查询：
| select Book=convert(varchar(45),replicate (' ', 2*rank) + title_id + replicate(' ', 35-2*rank)+': '), qty from v_title_rank order by rank |
结果：
| Book Qty ------------------------------------------- ---- MC2222 : 10 BU1032 : 60 BU7832 : 60 PS3333 : 60 PS1372 : 140 TC4203 : 140 TC7777 : 140 BU1111 : 250 PS2106 : 250 PS7777 : 250 PC1035 : 330 BU2075 : 420 MC3021 : 560 TC3218 : 560 PC8888 : 750 PS2091 : 1728 (16 row(s) affected) |
