Sie sind zurzeit offline. Es wird auf die erneute Herstellung einer Internetverbindung gewartet.

Dynamisches Nummerieren von Zeilen in einer SELECT Transact-SQL-Anweisung

Zusammenfassung
Dieser Artikel beschreibt, wie Sie bei der Ausführung einer SELECT-Anweisung Zeilen mit einer flexiblen Methode dynamisch in eine Rangfolge bringen. Dies ist möglicherweise die einzige Lösung und ist schneller als die prozedurale Lösung. Die Zeilennummerierung oder Rangfolge ist ein typisches prozedurales Problem. Die Lösungen basieren in der Regel auf Schleifen und temporären Tabellen, also auf SQL Server-Schleifen und -Cursorn. Diese Technik beruht auf einem Auto-Join. Die gewählte Beziehung ist in der Regel "ist größer als". Zählen Sie, wie oft die einzelnen Elemente einer bestimmten Datenmenge die Beziehung "ist größer als" erfüllen, wenn die Datenmenge mit sich selbst verglichen wird.

Hinweis Die folgenden Beispiele basieren auf der Datenbank pubs. Standardmäßig werden die Beispieldatenbanken Northwind und pubs nicht in SQL Server 2005 installiert. Diese Datenbanken können aus dem Microsoft Download Center heruntergeladen werden. Weitere Informationen hierzu finden Sie auf der folgenden Microsoft-Website:Nachdem Sie die Datei "SQL2000SampleDb.msi" heruntergeladen haben, extrahieren Sie die Datenbankskripts, indem Sie auf "SQL2000SampleDb.msi" doppelklicken. Standardmäßig extrahiert "SQL2000SampleDb.msi" die Datenbankskripts und die ReadMe-Datei in den folgenden Ordner:
C:\SQL Server 2000 Sample Databases
Folgen Sie den Anweisungen in der ReadMe-Datei, um die Installationsskripts auszuführen.

Wenn Sie mit SQL Server 2005 arbeiten

Microsoft empfiehlt, die Rangfolgefunktionen zu verwenden, die als neues Feature in SQL Server 2005 zur Verfügung stehen. Weitere Informationen zu den Rangfolgefunktionen finden Sie auf folgender Website des Microsoft Developer Network (MSDN):back to the top

Beispiel 1

In diesem Beispiel gilt:
  • Menge 1 ist "authors".
  • Menge 2 ist "authors".
  • Die Beziehung ist "Vor- und Nachnamen sind größer als".
  • Probleme mit Duplikaten können Sie vermeiden, indem Sie die Vor- und Nachnamen mit den anderen Vor- und Nachnamen vergleichen.
  • Zählen Sie, wie oft die Beziehung durch count(*) erfüllt ist.
Abfrage:
   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				
Verwenden Sie in SQL Server 2005 den folgenden Code.
   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 
Ergebnis:
   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 Zeile(n) betroffen)				
back to the top

Beispiel 2

In diesem Beispiel gilt:
  • Der Rang wird nach der Anzahl verkaufter Bücher vergeben.
  • Menge 1 ist die Anzahl der pro Filiale verkauften Bücher: select stor_id, qty=sum(qty) from sales group by stor_id.
  • Menge 2 ist die Anzahl der pro Filiale verkauften Bücher: select stor_id, qty=sum(qty) from sales group by stor_id.
  • Die Beziehung ist "die Anzahl der Bücher ist größer als".
  • Um Duplikate zu vermeiden, können Sie (beispielsweise) "price*qty" anstelle von "qty" vergleichen.
Abfrage:
   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				
Ergebnis:
   Rank     Stor_Id    Qty   ----     -------    ---   1         6380        8   2         7896      120   3         8042      240   4         7067      360   5         7066      625   6         7131      780   (6 Zeile(n) betroffen)				
Hinweis Die Werte in der Spalte Qty sind falsch. Jedoch ist die Rangfolge der Filialen nach Menge der verkauften Bücher korrekt. Dies ist ein Manko dieser Methode. Sie können sie dazu verwenden, die Rangfolge der Filialen zurückzugeben, wenn die falsche Menge im Ergebnis für Sie keine Rolle spielt.

Verwenden Sie in SQL Server 2005 den folgenden Code.
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
Ergebnis:
rank     stor_id  qty-------  -------  ------1        7131     1302        7066     1253        7067     904        8042     805        7896     606        6380     8(6 Zeile(n) betroffen)
Hinweis In SQL Server 2005 können Sie mit den Rangfolgefunktionen das korrekte Ergebnis für Rangfolge und Menge erhalten.

back to the top

Beispiel 3

In diesem Beispiel gilt:
  • Der Rang wird nach dem Ertrag der Verlage vergeben.
  • Menge 1 ist der Gesamtumsatz nach Verlag:
            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					
  • Menge 2 ist der Gesamtumsatz nach Verlag:
            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					
  • Die Beziehung ist "verdient mehr Geld als".
Abfrage:
   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				
Ergebnis:
   Rank     Pub_Id   Sales   ----     ------   --------   1         0736    1,961.85   2         0877    4,256.20   3         1389    7,760.85   (3 Zeile(n) betroffen)				
Hinweis Die Werte in der Spalte Sales sind falsch. Jedoch ist die Rangfolge der Verlage nach Ertrag korrekt.

Verwenden Sie in SQL Server 2005 den folgenden Code.
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
Ergebnis:
rank     pub_id  sales-------  ------  ---------1        1389    2586.952        0877    2128.103        0736    1961.85(3 Zeile(n) betroffen)				
Hinweis Mit den Rangfolgefunktionen erhalten Sie das korrekte Ergebnis für Rangfolge und Ertrag.

back to the top

Nachteile

  • Wegen des Cross-Join ist diese Methode nicht für eine große Anzahl von Zeilen geeignet. Sie funktioniert gut bei Hunderten von Zeilen. Bei großen Tabellen sollten Sie einen Index verwenden, um umfangreiche Suchvorgänge zu vermeiden.
  • Bei doppelten Werten funktioniert diese Methode nicht gut. Wenn Sie doppelte Werte vergleichen, treten Unterbrechungen in der Zeilennummerierung auf. Wenn Sie das vermeiden möchten, können Sie die Rangfolgespalte beim Einfügen des Ergebnisses in eine Kalkulationstabelle ausblenden; verwenden Sie stattdessen die Nummerierung der Kalkulationstabelle.

    Note Wenn Sie mit SQL Server 2005 arbeiten, können Sie die Funktion row_number() dazu verwenden, die laufende Nummer einer Zeile zurückzugeben, ungeachtet der doppelten Zeilen.
Beispiel:
   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
Ergebnis:
   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 Zeile(n) betroffen)				
back to the top

Vorteile

  • Sie können die Abfragen in Sichten und in der Ergebnisformatierung verwenden.
  • Sie können die rangniedrigeren Daten weiter nach rechts verschieben.
Beispiel 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				
Abfrage:
   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +           pub_id +           replicate(' ', 15-power(2,rank))+': '),           earnings=qty   from v_pub_rank				
Ergebnis:
   Publisher       Earnings   -------------   --------     0736          : 1,961.85       0877        : 4,256.20           1389    : 7,760.85				
Verwenden Sie in SQL Server 2005 den folgenden Code.
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
Ergebnis:
publisher            earnings-------------------- ---------------------  0736             : 1961.85    0877           : 2128.10        1389       : 2586.95(3 Zeile(n) betroffen)

Beispiel 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				
Abfrage:
   select  Book=convert(varchar(45),replicate (' ', 2*rank) +           title_id +           replicate(' ', 35-2*rank)+': '),           qty   from v_title_rank   order by rank				
Ergebnis:
   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 Zeile(n) betroffen)				
Verwenden Sie in SQL Server 2005 den folgenden Code.
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
Ergebnis:
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 Zeile(n) betroffen) 
back to the top
prodsql join rank formatting query
Eigenschaften

Artikelnummer: 186133 – Letzte Überarbeitung: 07/30/2013 12:06:00 – Revision: 7.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 KB186133
Feedback
/html>>async=""> var varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write("