วิธีการหมายเลขแถวในงบการ SELECT Transact-SQL แบบไดนามิก

การแปลบทความ การแปลบทความ
หมายเลขบทความ (Article ID): 186133 - ผลิตภัณฑ์ที่เกี่ยวข้องในบทความนี้
ขยายทั้งหมด | ยุบทั้งหมด

เนื้อหาบนหน้านี้

สรุป

บทความนี้อธิบายวิธีการจัดอันดับแถวแบบไดนามิกเมื่อคุณดำเนินการคำสั่ง SELECT โดยใช้วิธียืดหยุ่น ซึ่งอาจเป็นวิธีแก้ไขปัญหาที่เป็นไปได้เท่านั้น และซึ่งจะเร็วกว่าการแก้ไขปัญหาขั้นตอน แถวลำดับเลข หรือ ranking เป็นปัญหาที่ขั้นตอนทั่วไป วิธีแก้ไขปัญหาเป็นไปโดยทั่วไปตาม loops และตารางชั่วคราว ดังนั้น จะเป็นไปตาม SQL Server loops และเคอร์เซอร์ เทคนิคนี้จะขึ้นอยู่กับการเข้าร่วมโดยอัตโนมัติ มีความสัมพันธ์ที่เลือกโดยทั่วไป "ได้มากกว่า" นับจำนวนครั้งที่แต่ละองค์ประกอบของชุดเฉพาะของข้อมูลความสัมพันธ์ที่ตอบสนอง "มีมากกว่า" เมื่อมีการเปรียบเทียบชุดตัวเอง

หมายเหตุ:ตัวอย่างต่อไปนี้ขึ้นอยู่กับpubsdatabase. โดยค่าเริ่มต้น การnorthwindฐานข้อมูลตัวอย่างและpubsไม่มีการติดตั้งฐานข้อมูลตัวอย่างใน SQL Server 2005 ฐานข้อมูลเหล่านี้สามารถดาวน์โหลดได้จากศูนย์ดาวน์โหลดของไมโครซอฟท์ สำหรับข้อมูลเพิ่มเติม โปรดเยี่ยมชมเว็บไซต์ต่อไปนี้ของ Microsoft::
http://go.microsoft.com/fwlink/?linkid=30196
หลังจากที่คุณดาวน์โหลด SQL2000SampleDb.msi แยกสคริปต์ของฐานข้อมูลตัวอย่าง โดยการคลิกสองครั้งที่ SQL2000SampleDb.msi โดยค่าเริ่มต้น SQL2000SampleDb.msi จะแยกสคริปต์ฐานข้อมูลและแฟ้ม readme ลงในโฟลเดอร์ต่อไปนี้:
ฐานข้อมูลตัวอย่างของ Server 2000 C:\SQL
ทำตามคำแนะนำในแฟ้ม readme เพื่อที่เรียกใช้สคริปต์การติดตั้ง

ถ้าคุณใช้ SQL Server 2005

เราขอแนะนำให้ คุณใช้ฟังก์ชันการจัดอันดับที่มีให้เป็นคุณลักษณะใหม่ใน SQL Server 2005 สำหรับข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชันการจัดอันดับ แวะไปที่เว็บไซต์ของ Microsoft สำหรับนักพัฒนาเครือข่าย (MSDN) ต่อไปนี้:
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, qty=sum(qty) จากกลุ่มการขายโดย stor_id
  • ชุด 2 คือ หมายเลขของบัญชีขาย โดยการเก็บ: เลือก stor_id, qty=sum(qty) จากกลุ่มการขายโดย stor_id
  • มีความสัมพันธ์ "หมายเลขหนังสือได้มากกว่า"
  • เมื่อต้องการหลีกเลี่ยงการซ้ำ คุณสามารถเปรียบ (เป็นตัวอย่าง) เทียบราคา * 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)
				
หมายเหตุ:ค่าในการปริมาณคอลัมน์ไม่ถูกต้อง อย่างไรก็ตาม ช่วงของร้านค้าที่ขึ้นอยู่กับปริมาณของสมุดบัญชีขายถูกต้อง นี่คือ defect ของวิธีการนี้ คุณสามารถใช้วิธีนี้จะส่งกลับการจัดลำดับของร้านค้าหากภายใต้การคุณไม่ดูแลเกี่ยวกับปริมาณที่ไม่ถูกต้องในผลลัพธ์

ใช้รหัสดังต่อไปนี้ใน 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)
				
หมายเหตุ:คุณได้รับผลลัพธ์ที่ถูกต้องของการจัดลำดับและการกำไรเมื่อคุณใช้ฟังก์ชันการจัดอันดับ

drawbacks

  • เนื่องจากการเข้าร่วมไขว้ ซึ่งถูกออกแบบไม่ขึ้นสำหรับการทำงานกับจำนวนแถวมากขึ้น จะทำงานด้วยสำหรับเป็นร้อย ๆ รายการของแถว ในตารางที่มีขนาดใหญ่ ตรวจสอบให้แน่ใจว่าจะใช้ดัชนีเพื่อหลีกเลี่ยงการสแกนที่มีขนาดใหญ่
  • ซึ่งไม่ทำงานกับค่าที่ซ้ำกันด้วย เมื่อคุณเปรียบเทียบค่าที่ซ้ำกัน การกำหนดหมายเลขแถว discontinuous เกิดขึ้น ข้อมูลนี้จะไม่ทำงานที่คุณต้อง คุณสามารถหลีกเลี่ยงได้ โดยการซ่อนคอลัมน์อันดับเมื่อคุณแทรกผลลัพธ์ในกระดาษคำนวณ ใช้กระดาษคำนวณลำดับเลขแทน

    หมายเหตุ:ในกรณีที่คุณใช้ SQL Server 2005 คุณสามารถใช้การrow_number()ฟังก์ชันจะส่งกลับจำนวนแถว คำนึงถึงแถวที่ซ้ำกันตามลำดับ
ตัวอย่าง::
   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
ผลลัพธ์:
   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)
				
Use the following code in 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
Result:
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)

 

คุณสมบัติ

หมายเลขบทความ (Article ID): 186133 - รีวิวครั้งสุดท้าย: 17 กันยายน 2554 - Revision: 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 2005 Server Enterprise
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL 2005 Server Workgroup
Keywords: 
kbhowtomaster kbmt KB186133 KbMtth
แปลโดยคอมพิวเตอร์
ข้อมูลสำคัญ: บทความนี้แปลโดยซอฟต์แวร์การแปลด้วยคอมพิวเตอร์ของ Microsoft แทนที่จะเป็นนักแปลที่เป็นบุคคล Microsoft มีบทความที่แปลโดยนักแปลและบทความที่แปลด้วยคอมพิวเตอร์ เพื่อให้คุณสามารถเข้าถึงบทความทั้งหมดในฐานความรู้ของเรา ในภาษาของคุณเอง อย่างไรก็ตาม บทความที่แปลด้วยคอมพิวเตอร์นั้นอาจมีข้อบกพร่อง โดยอาจมีข้อผิดพลาดในคำศัพท์ รูปแบบการใช้ภาษาและไวยากรณ์ เช่นเดียวกับกรณีที่ชาวต่างชาติพูดผิดเมื่อพูดภาษาของคุณ Microsoft ไม่มีส่วนรับผิดชอบต่อความคลาดเคลื่อน ความผิดพลาดหรือความเสียหายที่เกิดจากการแปลเนื้อหาผิดพลาด หรือการใช้บทแปลของลูกค้า และ Microsoft มีการปรับปรุงซอฟต์แวร์การแปลด้วยคอมพิวเตอร์อยู่เป็นประจำ
ต่อไปนี้เป็นฉบับภาษาอังกฤษของบทความนี้:186133

ให้ข้อเสนอแนะ

 

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