文章編號: 186133 - 上次校閱: 2006年2月14日 - 版次: 5.4

如何動態地 SELECT Transact-SQL 陳述式中編號的資料列

中英文對照顯示按一下這裡顯示機器翻譯的中英文對照
機器翻譯檢視機器翻譯免責聲明
檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。

在此頁中

全部展開 | 全部摺疊

結論

本文將告訴您，如何利用有彈性的方法，這可能只可能的解決方案，而何者為程序性解決方案比快執行 SELECT 陳述式時，動態排名資料列。資料列編號或分級是典型的程序性問題。該解決方案通常根據迴圈和暫存資料表 ； 因此，它們根據 SQL Server 迴圈和游標。這項技術根據自動聯結。選擇的關聯通常是 「 是大於 」。Count 多少次特定的資料集的每個項目滿足關聯性 「 大於 」 何時集合相較於本身。

附註下列範例根據 pubs 資料庫。預設情況下，北風貿易 範例資料庫和 pubs 範例資料庫未安裝 SQL Server 2005 中。可從 Microsoft 下載中心 」 下載這些資料庫。如需詳細資訊請造訪下列 Microsoft 網站]：
http://go.microsoft.com/fwlink/?linkid=30196 (http://go.microsoft.com/fwlink/?linkid=30196)
下載 SQL2000SampleDb.msi 之後請連按兩下 SQL2000SampleDb.msi 解壓縮範例資料庫指令碼。預設情況下，SQL2000SampleDb.msi 會展開資料庫指令碼和讀我檔案到下列資料夾：
C:\SQL Server 2000 樣本資料庫
依照指示讀我檔案中的執行安裝指令碼。
回此頁最上方

如果您使用的 SQL Server 2005

我們建議您為 SQL Server 2005 中的新功能使用所提供的排名函式。如需有關排名函式的詳細資訊，請造訪下列 Microsoft 開發 o 人 h 員 ? 工 u 具 ? 網路 (MSDN) 網站]：
http://msdn2.microsoft.com/en-us/library/ms189798.aspx (http://msdn2.microsoft.com/en-us/library/ms189798.aspx)
回此頁最上方

範例 1

在這個範例中：
  • 集 1 是作者。
  • 集 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 rank
SQL Server 2005 中使用下列程式碼 
   select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname
   from authors a
   order by rank
結果： 
   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

在這個範例中：
  • 排名存放區的書籍的銷售數量。
  • 組 1 是銷售的存放區的書籍數目： 選取 stor_id 從銷售的分組方式 stor_id qty=sum(qty)。
  • 組 2 是銷售的存放區的書籍數目： 選取 stor_id 從銷售的分組方式 stor_id qty=sum(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 rank
結果: 
   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)
附註 [Qty] 欄中的值不正確。不過，根據書籍的銷售數量的存放區的排名是正確的。這是這個方法的缺失。您可以使用這個方法來傳回存放區的排名，如果您不在乎錯誤的數量，在結果中。

在 SQL Server 2005 中使用下列程式碼 
select row_number() over (order by qty desc) as rank,s1.stor_id,s1.qty
from (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
結果: 
rank     stor_id  qty
-------  -------  ------
1        7131     130
2        7066     125
3        7067     90
4        8042     80
5        7896     60
6        6380     8

(6 row(s) affected)
SQL Server 2005 中的 附註，您就可以接收等級和數量的正確的結果，當您使用的排序函式。

回此頁最上方

範例 3

在這個範例中：
  • 由其所得的排名出版商。
  • 集 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
  • 集 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
  • 關聯性是 earns 比多資金。
查詢： 
   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 rank
結果: 
   Rank     Pub_Id   Sales
   ----     ------   --------
   1         0736    1,961.85
   2         0877    4,256.20
   3         1389    7,760.85

   (3 row(s) affected)
附註 [銷售] 欄中的值不正確。不過，根據所得的出版商排名是正確的。

在 SQL Server 2005 中使用下列程式碼。
select rank() over (order by sales desc) as rank,s1.pub_id,s1.sales 
from (select t.pub_id, sales=sum(s.qty*t.price)
     from sales s inner join titles t
     on s.title_id=t.title_id
     where  t.price is not null
     group by t.pub_id) as s1
結果： 
rank     pub_id  sales
-------  ------  ---------
1        1389    2586.95
2        0877    2128.10
3        0736    1961.85

(3 row(s) affected)
附註 您收到排序及贏得當您使用的排序函式的正確的結果。

回此頁最上方

缺點

  • 因為的交叉聯結這不被設計來處理大量的資料列。它非常適合數百個資料列。確定大型資料表上使用索引來避免大型掃描]。
  • 這無法運作於重複的值。當您比較重複值間斷的資料列編號就會發生。如果這不是您想要的行為，您可以避免它藉由隱藏 [次序] 資料行，當您在試算表中插入結果 ； 使用試算表改編號。

    附註如果您使用的 SQL Server 2005，您可以使用 row_number() 函數來傳回列不論重複的資料列的循序號碼。
Example: 
   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 rank
Result: 
   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
SQL Server 2005 中使用下列程式碼 
CREATE VIEW v_pub_rank
AS
select rank() over (order by sales) as rank,s1.pub_id,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) as s1
GO

select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
	pub_id + replicate(' ', 15-power(2,rank))+': '),
	earnings=sales
from v_pub_rank order by rank
GO
結果: 
publisher            earnings
-------------------- ---------------------
  0736             : 1961.85
    0877           : 2128.10
        1389       : 2586.95

(3 row(s) affected)

範例 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)
SQL Server 2005 中使用下列程式碼 
CREATE VIEW v_title_rank
AS
select rank() over (order by qty) as rank, s1.title_id,s1.qty
from (select title_id, qty=sum(qty) from sales group by title_id) as s1
GO

select Book=convert(varchar(45),replicate (' ', 2*rank) +
title_id + replicate(' ', 35-2*rank)+': '), qty
from v_title_rank
order by rank
GO
結果： 
Book                                          qty
--------------------------------------------- -----------
  MC2222                                 :    10
    BU1032                               :    15
    BU7832                               :    15
    PS3333                               :    15
          TC4203                         :    20
          TC7777                         :    20
          PS1372                         :    20
                BU1111                   :    25
                PS7777                   :    25
                PS2106                   :    25
                      PC1035             :    30
                        BU2075           :    35
                          MC3021         :    40
                          TC3218         :    40
                              PC8888     :    50
                                PS2091   :    108

(16 row(s) affected)
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
回此頁最上方
關鍵字：?
kbmt kbhowtomaster KB186133 KbMtzh
回此頁最上方
機器翻譯機器翻譯
重要：本文是以 Microsoft 機器翻譯軟體翻譯而成，而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章，讓使用者可以依其使用語言使用知識庫中的所有文章。但是，機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤，就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。
按一下這裡查看此文章的英文版本：186133? (http://support.microsoft.com/kb/186133/en-us/ )
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。