技巧

热门文章

最新文章

联系方式

Access交流网(免费Access交流）

QQ：18449932 

网  址：www.access-cn.com

DAO/链接表/ADO/ADP

如何在Select语句中动态对行进行编号(T-SQL)

作    者：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)





发布人:zhuyiwen…-AccessFaq  
分享到：
点击次数：  更新时间：2004-11-23 22:58:08  【打印此页】  【关闭】
上一条：如何在自定义函数中实现枚举值  下一条：使用SQL语句创建表中的自动编号字段并定义为主键


相关文章

热门文章

热门产品