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

文章编号: 186133 - 查看本文应用于的产品
本文的发布号曾为 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                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)

缺点

  • 因为有交叉联接，所以该方法不适用于处理大量行。它适用于处理几百行。对于大型表，一定要使用索引以避免进行大范围的搜索。
  • 它不能正常处理重复值。当比较重复值时，会出现不连续的行编号。如果不希望出现这种现象，可以在电子表格中插入结果时隐藏排序列，而是使用电子表格编号。
示例： 
   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)
回到顶端 | 提供反馈

属性

文章编号: 186133 - 最后修改: 2011年5月15日 - 修订: 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
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性，不作任何声明。 所有该等文件及有关图形均"依样"提供，而不带任何性质的保证。Microsoft和/或其各供应商特此声明，对所有与该等信息有关的保证和条件不负任何责任，该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下，在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中，Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。
回到顶端 | 提供反馈

提供反馈

 
回到顶端回到顶端