[HOWTO] Select ステートメントで動的に行番号を付ける方法

文書翻訳 文書翻訳
文書番号: 186133 - 対象製品
すべて展開する | すべて折りたたむ

目次

概要

この資料では、SELECT ステートメントを実行するときに動的に行をランク付けする、柔軟性のある方法を説明します。この方法がおそらく唯一のソリューションで、手続き的なソリューションよりも高速です。行のナンバリングやランク付けは通常、手続き的に処理します。一般には、ループと一時テーブル、つまり、SQL Server のループとカーソルを使用します。このテクニックは、自動結合に基づいています。リレーションシップは、通常 "より大きい" を選択します。特定のデータ セットをそのセット自体と比較したときに、データ セットの各要素が "より大きい" というリレーションシップを満たす回数を数えます。

: 以下の例では、pubs データベースを使用しています。

例 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 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 件処理されました)
				

例 2

この例では、以下の設定を使用します。
  • 本の売上部数順に書店をランク付けします。
  • セット 1 は書店で売れた本の冊数です。
    select stor_id, qty=sum(qty) from sales group by stor_id
    
  • セット 2 は書店で売れた本の冊数です。
    select stor_id, qty=sum(qty) from sales group by stor_id
    
  • リレーションシップは "売れた本の冊数がより多い" です。
  • 重複データを避けるには、(一例として) qty ではなく price*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 件処理されました)
				
上記の例の中には、FROM 句で派生テーブルを使用しているために Microsoft SQL Server 6.5 より前のバージョンでは機能しないものもあります。

例 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
    					
  • リレーションシップは "総売上高がより多い" です。
クエリ :
   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 件処理されました)
				

デメリット

  • この例はクロス結合を使用しているため、多くの行を処理するのには不向きです。数百行では適切に動作します。大きいテーブルを使用する場合は、インデックスを使用して大量のスキャンを避けるようにします。
  • この例は重複する値に対して適切に動作しません。重複する値を比較すると、行に付けられる番号が連続しなくなります。この問題を回避するには、ワークシートに結果を挿入するときにランクの列を非表示にします。代わりに、ワークシートの番号付けを使用します。
:
   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 件処理されました)
				

メリット

  • これらのクエリをビューおよび結果形式で使用できます。
  • 低いランクのデータを右に移動して表示できます。
例 1 :
   CREATE VIEW v_pub_rank
AS
   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
 				
クエリ :
   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
           pub_id +
           replicate(' ', 15-power(2,rank))+': '),
           earnings=sales
   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 件処理されました)
				

関連情報

この資料は米国 Microsoft Corporation から提供されている Knowledge Base の Article ID 186133 (最終更新日 2003-05-22) を基に作成したものです。

この資料に含まれているサンプル コード/プログラムは英語版を前提に書かれたものをありのままに記述しており、日本語環境での動作は確認されておりません。

プロパティ

文書番号: 186133 - 最終更新日: 2011年5月17日 - リビジョン: 5.0
この資料は以下の製品について記述したものです。
  • 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
キーワード:?
kbsqlmanagementtools kbhowtomaster KB186133
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com