你目前正处于脱机状态,正在等待 Internet 重新连接

如何在 SELECT 语句中动态地对行进行编号

本文的发布号曾为 CHS186133
概要
本文介绍如何在执行 SELECT 语句时动态地对行排序。这是一种很灵活的方法,也可能是唯一可能的解决方法。 它比过程解决方法更快一些。行编号或排序是一个典型的过程问题。其解决方法通常是基于循环函数和临时表,即 SQL Server 循环函数和游标函数。此技术基于自动联接技术。所选的关系一般为“大于”。其目的是当集合与本身比较时,统计特定数据集合的每个元素有多少次实现了“大于”关系。

注意:以下示例均基于 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                Abraham2         Blotchet-Halls        Reginald3         Carson                Cheryl4         DeFrance              Michel5         del Castillo          Innes6         Dull                  Ann7         Greene                Morningstar8         Green                 Marjorie9         Gringlesby            Burt10         Hunter                Sheryl11         Karsen                Livia12         Locksley              Charlene13         MacFeather            Stearns14         McBadden              Heather15         O'Leary               Michael16         Panteley              Sylvia17         Ringer                Albert18         Ringer                Anne19         Smith                 Meander20         Straight              Dean21         Stringer              Dirk22         White                 Johnson23         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)				
返回页首

缺点

  • 因为有交叉联接,所以该方法不适用于处理大量行。它适用于处理几百行。对于大型表,一定要使用索引以避免进行大范围的搜索。
  • 它不能正常处理重复值。当比较重复值时,会出现不连续的行编号。如果不希望出现这种现象,可以在电子表格中插入结果时隐藏排序列,而是使用电子表格编号。
示例
   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        104       BU1032        604       BU7832        604       PS3333        607       PS1372       1407       TC4203       1407       TC7777       14010      BU1111       25010      PS2106       25010      PS7777       25011      PC1035       33012      BU2075       42014      MC3021       56014      TC3218       56015      PC8888       75016      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                                 :      10BU1032                           :      60BU7832                           :      60PS3333                           :      60PS1372                     :     140TC4203                     :     140TC7777                     :     140BU1111               :     250PS2106               :     250PS7777               :     250PC1035             :     330BU2075           :     420MC3021       :     560TC3218       :     560PC8888     :     750PS2091   :    1728(16 row(s) affected)				
返回页首
prodsql join rank formatting query
属性

文章 ID:186133 - 上次审阅时间:05/15/2011 18:07:00 - 修订版本: 5.0

Microsoft SQL Server 2000 标准版, Microsoft SQL Server 4.21a 标准版, Microsoft SQL Server 6.0 标准版, Microsoft SQL Server 6.5 标准版, Microsoft SQL Server 7.0 标准版, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL 2005 Server Enterprise, Microsoft SQL Server 2005 Express Edition, Microsoft SQL 2005 Server Workgroup

  • kbsqlmanagementtools kbhowtomaster KB186133
反馈