ID Artikel: 186133 - Kajian Terakhir: 16 September 2011 - Revisi: 2.0

Bagaimana secara dinamis nomor baris dalam sebuah pernyataan pilih Transact-SQL

Klik disini untuk menampilkan sumber Inggris dan terjemahan oleh mesin secara berdampingan

Peringatan: artikel ini adalah terjemahan oleh mesin

Melihat produk di mana artikel ini berlaku.

Tips SistemThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

Artikel ini menjelaskan cara untuk secara dinamis peringkat baris ketika Anda melakukan pernyataan Pilih dengan menggunakan metode yang fleksibel, yang mungkin hanya solusi dan yang lebih cepat daripada solusi prosedural. Baris penomoran atau peringkat adalah masalah prosedural yang khas. Solusi-solusi yang biasanya berdasarkan loop dan meja sementara; oleh karena itu, mereka didasarkan pada SQL Server loop dan kursor. Teknik ini didasarkan pada auto bergabung. Dipilih hubungan biasanya adalah "lebih besar daripada." Menghitung berapa kali setiap elemen set tertentu data memenuhi hubungan "lebih besar dari" ketika set dibandingkan dengan itu sendiri.

Catatan Contoh berikut adalah berdasarkan pub database. Secara default, Northwind database contoh dan pub database contoh tidak diinstal di SQL Server 2005. Database ini dapat di-download dari Pusat Download Microsoft. Untuk informasi selengkapnya, kunjungi situs Web Microsoft berikut ini:

http://Go.Microsoft.com/fwlink/?LinkId=30196 (http://go.microsoft.com/fwlink/?linkid=30196)

Setelah Anda men-download SQL2000SampleDb.msi, mengambil sampel database script dengan mengklik dua kali SQL2000SampleDb.msi. Secara default, SQL2000SampleDb.msi akan mengekstrak script database dan readme file ke dalam map berikut:

C:\SQL Server 2000 sampel database

Ikuti petunjuk dalam readme file untuk menjalankan script instalasi.

Kembali ke atas

Jika Anda menggunakan SQL Server 2005

Kami menyarankan agar Anda menggunakan fungsi peringkat yang disediakan sebagai fitur baru dalam SQL Server 2005. Untuk informasi lebih lanjut tentang peringkat fungsi, kunjungi Web site Microsoft Developer Network (MSDN) berikut:

http://msdn2.Microsoft.com/en-us/library/ms189798.aspx (http://msdn2.microsoft.com/en-us/library/ms189798.aspx)

Kembali ke atas

Contoh 1

Dalam contoh ini:

Set 1 adalah penulis.
Set 2 adalah penulis.
Hubungan adalah "nama pertama dan terakhir lebih besar daripada."
Anda dapat menghindari masalah duplikat dengan membandingkan pertama + terakhir nama lain pertama + terakhir nama.
Menghitung jumlah kali hubungan dipenuhi oleh Count(*).

Permintaan:

   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

Gunakan kode berikut dalam 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

Hasil:

   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)

Kembali ke atas

Contoh 2

Dalam contoh ini:

Peringkat toko oleh jumlah buku-buku yang dijual.
Set 1 adalah jumlah buku-buku yang dijual oleh toko: Pilih stor_id, qty=Sum(qty) dari penjualan grup oleh stor_id.
Set 2 adalah jumlah buku-buku yang dijual oleh toko: Pilih stor_id, qty=Sum(qty) dari penjualan grup oleh stor_id.
Hubungan adalah "jumlah buku-buku lebih besar daripada."
Untuk menghindari duplikat, Anda dapat (sebagai contoh) membandingkan harga * qty bukannya qty.

Permintaan:

   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

Hasil:

   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)

Catatan Nilai-nilai di Qty kolom tidak benar. Namun, peringkat berdasarkan jumlah buku-buku yang dijual toko benar. Ini adalah cacat metode ini. Anda dapat menggunakan metode ini untuk kembali peringkat toko jika Anda tidak peduli tentang kuantitas yang salah dalam hasil.

Gunakan kode berikut dalam 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

Hasil:

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)

Catatan Dalam SQL Server 2005, Anda dapat menerima hasil benar peringkat dan kuantitas ketika Anda menggunakan fungsi peringkat.

Kembali ke atas

Contoh 3

Dalam contoh ini:

Peringkat penerbit oleh penghasilan mereka.

Set 1 adalah total penjualan oleh penerbit:

        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 adalah total penjualan oleh penerbit:

        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

Hubungan adalah "mendapatkan lebih banyak uang daripada."

Permintaan:

   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

Hasil:

   Rank     Pub_Id   Sales
   ----     ------   --------
   1         0736    1,961.85
   2         0877    4,256.20
   3         1389    7,760.85

   (3 row(s) affected)

Catatan Nilai-nilai di Penjualan kolom tidak benar. Namun, peringkat penerbit berdasarkan pendapatan benar.

Gunakan kode berikut dalam 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

Hasil:

rank     pub_id  sales
-------  ------  ---------
1        1389    2586.95
2        0877    2128.10
3        0736    1961.85

(3 row(s) affected)

Catatan Anda menerima hasil benar peringkat dan mendapatkan ketika Anda menggunakan fungsi peringkat.

Kembali ke atas

Kekurangan

Karena dari bergabung salib, ini tidak dirancang untuk bekerja dengan sejumlah besar baris. It works well untuk ratusan baris. Pada besar tabel, pastikan untuk menggunakan indeks untuk menghindari scan besar.
Ini tidak bekerja dengan baik dengan nilai-nilai duplikat. Ketika Anda membandingkan nilai-nilai duplikat, tersekat-sekat baris penomoran terjadi. Jika hal ini tidak perilaku yang Anda inginkan, Anda dapat menghindari dengan menyembunyikan kolom peringkat ketika Anda Masukkan hasilnya dalam spreadsheet; menggunakan penomoran spreadsheet sebaliknya.

Catatan Jika Anda menggunakan SQL Server 2005, Anda dapat menggunakan row_number() fungsi untuk kembali jumlah berurutan berturut-turut, terlepas dari duplikat.

Contoh:

   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

Hasil:

   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)

Kembali ke atas

Keuntungan

Anda dapat menggunakan ini queries dalam pandangan dan hasil format.
Anda dapat memindahkan data berperingkat rendah lebih kanan.

Contoh 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

Permintaan:

   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
           pub_id +
           replicate(' ', 15-power(2,rank))+': '),
           earnings=qty
   from v_pub_rank

Hasil:

   Publisher       Earnings
   -------------   --------
     0736          : 1,961.85
       0877        : 4,256.20
           1389    : 7,760.85

Gunakan kode berikut dalam 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

Hasil:

publisher            earnings
-------------------- ---------------------
  0736             : 1961.85
    0877           : 2128.10
        1389       : 2586.95

(3 row(s) affected)

Contoh 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

Permintaan:

   select  Book=convert(varchar(45),replicate (' ', 2*rank) +
           title_id +
           replicate(' ', 35-2*rank)+': '),
           qty
   from v_title_rank
   order by rank

Hasil:

   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)

Gunakan kode berikut dalam 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

Hasil:

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)

Kembali ke atas

Berlaku bagi:

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

Kembali ke atas

kbsqlsetup kbhowtomaster kbmt KB186133 KbMtid

Kembali ke atas

Penerjemahan Mesin

PENTING: Artikel ini diterjemahkan menggunakan perangkat lunak mesin penerjemah Microsoft dan bukan oleh seorang penerjemah. Microsoft menawarkan artikel yang diterjemahkan oleh seorang penerjemah maupun artikel yang diterjemahkan menggunakan mesin sehingga Anda akan memiliki akses ke seluruh artikel baru yang diterbitkan di Pangkalan Pengetahuan (Knowledge Base) dalam bahasa yang Anda gunakan. Namun, artikel yang diterjemahkan menggunakan mesin tidak selalu sempurna. Artikel tersebut mungkin memiliki kesalahan kosa kata, sintaksis, atau tata bahasa, hampir sama seperti orang asing yang berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab terhadap akurasi, kesalahan atau kerusakan yang disebabkan karena kesalahan penerjemahan konten atau penggunaannya oleh para pelanggan. Microsoft juga sering memperbarui perangkat lunak mesin penerjemah.

Klik disini untuk melihat versi Inggris dari artikel ini:186133� (http://support.microsoft.com/kb/186133/en-us/ )

Kembali ke atas