Làm th? nào đ? t? đ?ng s? hàng trong m?t tuyên b? ch?n Transact-SQL

D?ch tiêu đ? D?ch tiêu đ?
ID c?a bài: 186133 - Xem s?n ph?m mà bài này áp d?ng vào.
Bung t?t c? | Thu g?n t?t c?

? Trang này

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:
http://go.Microsoft.com/fwlink/?LinkId=30196
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):
http://msdn2.Microsoft.com/en-us/Library/ms189798.aspx

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)
				

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.qty
from (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
Kết quả:
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)
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.

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.95
2        0877    2128.10
3        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.

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)
				

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_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
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_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
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)

 

Thu?c tính

ID c?a bài: 186133 - L?n xem xét sau cùng: 17 Tháng Chín 2011 - Xem xét l?i: 3.0
Áp d?ng
  • 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
T? khóa: 
kbsqlsetup kbhowtomaster kbmt KB186133 KbMtvi
Máy d?ch
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

Cung cấp Phản hồi

 

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