Làm thế nào để tự động số hàng trong một tuyên bố chọn Transact-SQL

QUAN TRỌNG: Bài viết này được dịch bằng phần mềm dịch máy của Microsoft chứ không phải do con người dịch. Microsoft cung cấp các bài viết do con người dịch và cả các bài viết do máy dịch để bạn có thể truy cập vào tất cả các bài viết trong Cơ sở Kiến thức của chúng tôi bằng ngôn ngữ của bạn. Tuy nhiên, bài viết do máy dịch không phải lúc nào cũng hoàn hảo. Loại bài viết này có thể chứa các sai sót về từ vựng, cú pháp hoặc ngữ pháp, giống như một người nước ngoài có thể mắc sai sót khi nói ngôn ngữ của bạn. Microsoft không chịu trách nhiệm về bất kỳ sự thiếu chính xác, sai sót hoặc thiệt hại nào do việc dịch sai nội dung hoặc do hoạt động sử dụng của khách hàng gây ra. Microsoft cũng thường xuyên cập nhật phần mềm dịch máy này.

Nhấp chuột vào đây để xem bản tiếng Anh của bài viết này:186133
TÓM TẮT
Bài viết này mô tả làm thế nào để tự động xếp hạng hàng khi bạn thực hiện một tuyên bố chọn bằng cách sử dụng một phương pháp linh hoạt, có thể là các chỉ có giải pháp có thể và đó là nhanh hơn so với các giải pháp về thủ tục. Hàng đánh số hoặc xếp hạng là một vấn đề điển hình về thủ tục. Các giải pháp thường dựa trên vòng và bảng tạm thời; do đó, chúng được dựa trên máy chủ SQL vòng và con chạy. Kỹ thuật này dựa trên một tham gia tự động. Các lựa chọn mối quan hệ là điển hình "là lớn hơn." Đếm bao nhiêu lần mỗi phần tử của một tập dữ liệu cụ thể thực hiện tốt mối quan hệ "là lớn hơn" khi bộ so với chính nó.

Chú ý Ví dụ sau đây được dựa trên các quán rượu cơ sở dữ liệu. Theo mặc định, các Northwind mẫu cơ sở dữ liệu và các quán rượu mẫu cơ sở dữ liệu không được cài đặt trong SQL Server 2005. Các cơ sở dữ liệu có thể được tải về từ Microsoft Download Center. Để biết thêm chi tiết, ghé thăm Web site sau của Microsoft:Sau khi bạn tải về SQL2000SampleDb.msi, giải nén tập lệnh cơ sở dữ liệu mẫu bằng cách bấm đúp vào SQL2000SampleDb.msi. Theo mặc định, SQL2000SampleDb.msi sẽ giải nén tập lệnh cơ sở dữ liệu và một tập tin readme vào thư mục sau đây:
C:\SQL Server 2000 mẫu cơ sở dữ liệu
Làm theo hướng dẫn trong tập tin readme chạy kịch bản cài đặt.

Nếu bạn đang sử dụng SQL Server 2005

Chúng tôi đề nghị bạn sử dụng chức năng xếp hạng được cung cấp như một tính năng mới trong SQL Server 2005. Để biết thêm chi tiết về các chức năng bảng xếp hạng, ghé thăm Web site sau của Microsoft Developer Network (MSDN):back to the top

Ví dụ 1

Trong ví dụ này:
  • Tập 1 là tác giả.
  • Đặt 2 là tác giả.
  • Mối quan hệ là «đầu tiên và cuối tên là lớn hơn» hơn."
  • Bạn có thể tránh được vấn đề trùng lặp bằng cách so sánh đầu tiên + cuối cùng tên khác trước tiên + cuối cùng tên.
  • Đếm số lần mối quan hệ này được hoàn thành bởi Count(*).
Truy vấn:
   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				
Sử dụng đoạn mã sau trong 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 
Kết quả:
   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)				
back to the top

Ví dụ 2

Trong ví dụ này:
  • Xếp hạng các cửa hàng bởi số lượng sách bán được.
  • Tập 1 là số lượng sách bán bởi cửa hàng: chọn stor_id, Qty=sum(Qty) từ nhóm bán hàng của stor_id.
  • Đặt 2 là số lượng sách bán bởi cửa hàng: chọn stor_id, Qty=sum(Qty) từ nhóm bán hàng của stor_id.
  • Mối quan hệ là "số lượng sách lớn hơn hơn."
  • So để tránh bản sao, bạn có thể (như là một ví dụ) sánh giá * qty thay vì của qty.
Truy vấn:
   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				
Kết quả:
   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)				
Chú ý Các giá trị trong các Qty cột là không chính xác. Tuy nhiên, việc xếp hạng của các cửa hàng dựa trên số lượng sách bán ra là chính xác. Đây là một khiếm khuyết của phương pháp này. Bạn có thể sử dụng phương pháp này để trở về việc xếp hạng của các cửa hàng nếu bạn không quan tâm về số lượng sai trong kết quả.

Sử dụng đoạn mã sau trong SQL Server 2005.
select row_number() over (order by qty desc) as rank,s1.stor_id,s1.qtyfrom (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
Kết quả:
rank     stor_id  qty-------  -------  ------1        7131     1302        7066     1253        7067     904        8042     805        7896     606        6380     8(6 row(s) affected)
Chú ý Trong SQL Server 2005, bạn có thể nhận được kết quả chính xác của việc xếp hạng và số lượng khi bạn sử dụng các chức năng xếp hạng.

back to the top

Ví dụ 3

Trong ví dụ này:
  • Đánh giá các nhà xuất bản của các khoản thu nhập của họ.
  • Tập 1 là tổng doanh số của nhà xuất bản:
            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					
  • Đặt 2 là tổng doanh số của nhà xuất bản:
            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					
  • Mối quan hệ là "kiếm được nhiều tiền hơn."
Truy vấn:
   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				
Kết quả:
   Rank     Pub_Id   Sales   ----     ------   --------   1         0736    1,961.85   2         0877    4,256.20   3         1389    7,760.85   (3 row(s) affected)				
Chú ý Các giá trị trong các Doanh số cột là không chính xác. Tuy nhiên, việc xếp hạng của các nhà xuất bản dựa vào các khoản thu nhập là đúng.

Sử dụng đoạn mã sau trong 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
Kết quả:
rank     pub_id  sales-------  ------  ---------1        1389    2586.952        0877    2128.103        0736    1961.85(3 row(s) affected)				
Chú ý Bạn nhận được kết quả chính xác của việc xếp hạng và thu nhập khi bạn sử dụng các chức năng bảng xếp hạng.

back to the top

Nhược điểm

  • Vì của tham gia chéo, điều này không được thiết kế để làm việc với một số lượng lớn các hàng. Nó hoạt động tốt cho hàng trăm hàng. Ngày lớn bảng, hãy chắc chắn để sử dụng một chỉ số để tránh lớn quét.
  • Điều này không làm việc tốt với các giá trị trùng lặp. Khi bạn so sánh các giá trị trùng lặp, gián đoạn dòng số xảy ra. Nếu điều này không phải là hành vi mà bạn muốn, bạn có thể tránh nó bằng cách ẩn cột thứ hạng khi bạn chèn kết quả trong một bảng tính; sử dụng bảng tính cách đánh số Thay vào đó.

    Chú ý Nếu bạn đang sử dụng SQL Server 2005, bạn có thể sử dụng các row_number() chức năng để trở về số liên tiếp, bất kể của các hàng trùng lặp, tuần tự.
Ví dụ:
   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
Kết quả:
   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)				
back to the top

Lợi ích

  • Bạn có thể sử dụng các truy vấn này trong quan điểm và kết quả định dạng.
  • Bạn có thể thay đổi dữ xếp hạng thấp hơn liệu nhiều hơn để các bên phải.
Ví dụ 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				
Truy vấn:
   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +           pub_id +           replicate(' ', 15-power(2,rank))+': '),           earnings=qty   from v_pub_rank				
Kết quả:
   Publisher       Earnings   -------------   --------     0736          : 1,961.85       0877        : 4,256.20           1389    : 7,760.85				
Sử dụng đoạn mã sau trong SQL Server 2005.
CREATE VIEW v_pub_rankASselect 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 s1GOselect  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +	pub_id + replicate(' ', 15-power(2,rank))+': '),	earnings=salesfrom v_pub_rank order by rankGO
Kết quả:
publisher            earnings-------------------- ---------------------  0736             : 1961.85    0877           : 2128.10        1389       : 2586.95(3 row(s) affected)

Ví dụ 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				
Truy vấn:
   select  Book=convert(varchar(45),replicate (' ', 2*rank) +           title_id +           replicate(' ', 35-2*rank)+': '),           qty   from v_title_rank   order by rank				
Kết quả:
   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)				
Sử dụng đoạn mã sau trong SQL Server 2005.
CREATE VIEW v_title_rankASselect rank() over (order by qty) as rank, s1.title_id,s1.qtyfrom (select title_id, qty=sum(qty) from sales group by title_id) as s1GOselect Book=convert(varchar(45),replicate (' ', 2*rank) +title_id + replicate(' ', 35-2*rank)+': '), qtyfrom v_title_rankorder by rankGO
Kết quả:
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) 
back to the top
prodsql tham gia xếp hạng định dạng truy vấn

Cảnh báo: Bài viết này được dịch tự động

Thuộc tính

ID Bài viết: 186133 - Xem lại Lần cuối: 09/17/2011 22:57:00 - Bản sửa đổi: 3.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

  • kbsqlsetup kbhowtomaster kbmt KB186133 KbMtvi
Phản hồi