V příkazu Transact-SQL vyberte dynamicky číslování řádků

Překlady článku Překlady článku
ID článku: 186133 - Produkty, které se vztahují k tomuto článku.
Rozbalit všechny záložky | Minimalizovat všechny záložky

Na této stránce

Souhrn

Tento článek popisuje, jak dynamicky pořadí řádků při provádění příkazu SELECT pomocí flexibilní metody, které mohou být pouze možné řešení a která je rychlejší než postup řešení. Řádek číslování nebo hodnocení je typické procesní otázce. Řešení jsou obvykle založeny na smyčky a dočasných tabulek; Proto jsou založeny na serveru SQL Server smyček a kurzory. Tato technika je založena na spojení auto. Vybrané relace je obvykle "je větší než." Počet kolikrát každý prvek určité sady dat splňuje vztah "je větší než" kdy sada je ve srovnání s sám.

Poznámka: Následující příklady jsou založeny na databáze pubs . Standardně nejsou nainstalovány ukázkové databáze Northwind a ukázkové databáze pubs serveru SQL Server 2005. Tyto databáze lze stáhnout z Microsoft Download Center. Další informace naleznete na následujícím webu společnosti Microsoft:
http://go.microsoft.com/fwlink/?LinkId=30196
Po stažení SQL2000SampleDb.msi extrakce poklepáním SQL2000SampleDb.msi ukázkové skripty databáze. Ve výchozím nastavení bude SQL2000SampleDb.msi extrahovat databázové skripty a soubor readme do následující složky:
C:\SQLServer2000 ukázkové databáze
Postupujte podle pokynů v souboru readme pro spuštění instalační skripty.

Pokud používáte SQL Server 2005

Doporučujeme použít funkce řazení, které jsou k dispozici nové funkce v SQL Server 2005. Další informace o funkcích řazení naleznete následujícím webu Microsoft Developer Network (MSDN):
http://msdn2.microsoft.com/en-us/library/ms189798.aspx

Příklad 1

V tomto příkladu:
  • Autoři je sada 1.
  • Autoři je sada 2.
  • Vztah je "příjmení a jména jsou greaterthan."
  • Duplicitní problému se můžete vyhnout tím, že první + poslední názvy jiných první + poslední názvy.
  • Počet případů, kdy je vztah bycount(*) splněny.
Dotaz:
   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
				
V SQL Server 2005, použijte následující kód.
   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 
Výsledek:
   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)
				

Příklad 2

V tomto příkladu:
  • Pořadí obchodů podle počtu prodaných knih.
  • Počet knih prodávaných úložiště je sada 1: Vyberte stor_id,qty=sum(qty) prodejní skupiny podle id_prod.
  • Počet knih prodávaných úložiště je sada 2: vybrat z prodejní skupiny stor_id,qty=sum(qty) id_prod.
  • Vztah je "počet knih je greaterthan."
  • Aby se zabránilo duplicitní položky, lze (jako příklad) compareprice * množství místo množ.
Dotaz:
   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
				
Výsledek:
   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)
				
Poznámka: Hodnoty ve sloupci množství jsou nesprávné. Hodnocení obchodů na základě množství prodaných knih je však správný. Je to vada této metody. Tímto způsobem můžete vrátit hodnocení obchodů, pokud nechcete o chybné množství ve výsledku.

V SQL Server 2005, použijte následující kód.
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
Výsledek:
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)
Poznámka: V SQL Server 2005 můžete obdržet správný výsledek hodnocení a množství, při použití funkce pořadí.

Příklad 3

V tomto příkladu:
  • Vydavatelů pořadí podle jejich příjmů.
  • Celkový prodej vydavatelem je sada 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
    					
  • Sada 2 je celkový prodej vydavatelem:
            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
    					
  • Vztah je "připadá více peněz než."
Dotaz:
   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
				
Výsledek:
   Rank     Pub_Id   Sales
   ----     ------   --------
   1         0736    1,961.85
   2         0877    4,256.20
   3         1389    7,760.85

   (3 row(s) affected)
				
Poznámka: Hodnoty ve sloupci Prodej jsou nesprávné. Je však správné řazení založené na příjmy vydavatelů.

V SQL Server 2005, použijte následující kód.
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
Výsledek:
rank     pub_id  sales
-------  ------  ---------
1        1389    2586.95
2        0877    2128.10
3        0736    1961.85

(3 row(s) affected)
				
Poznámka: Zobrazí správný výsledek hodnocení a získávat při použití funkce pořadí.

Nevýhody

  • Z důvodu křížové spojení není určen pro workingwith velký počet řádků. Je vhodný pro stovky řádků. Na largetables Přesvědčte se, zda použít index, aby se zabránilo velké kontroly.
  • To nefunguje správně s duplicitními hodnotami. Při youcompare duplicitní hodnoty, číslování nekontinuální řádek vyvolá. Pokud notthe chování, které chcete, se můžete vyhnout skrytím sloupce pořadí při youinsert výsledek v tabulce; pomocí tabulky numberinginstead.

    Poznámka: Pokud používáte SQL Server 2005, můžete row_number() funkce vrátí pořadové číslo řádku, bez ohledu na duplicitní řádky.
Příklad:
   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
Výsledek:
   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)
				

Výhody

  • Můžete použít tyto dotazy v zobrazení a resultformatting.
  • Můžete posunout data nižší hodnocení více na theright.
Příklad 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
				
Dotaz:
   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +
           pub_id +
           replicate(' ', 15-power(2,rank))+': '),
           earnings=qty
   from v_pub_rank
				
Výsledek:
   Publisher       Earnings
   -------------   --------
     0736          : 1,961.85
       0877        : 4,256.20
           1389    : 7,760.85
				
V SQL Server 2005, použijte následující kód.
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
Výsledek:
publisher            earnings
-------------------- ---------------------
  0736             : 1961.85
    0877           : 2128.10
        1389       : 2586.95

(3 row(s) affected)

Příklad 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
				
Dotaz:
   select  Book=convert(varchar(45),replicate (' ', 2*rank) +
           title_id +
           replicate(' ', 35-2*rank)+': '),
           qty
   from v_title_rank
   order by rank
				
Výsledek:
   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)
				
V SQL Server 2005, použijte následující kód.
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
Výsledek:
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)

 

Vlastnosti

ID článku: 186133 - Poslední aktualizace: 10. října 2013 - Revize: 8.0
Informace v tomto článku jsou určeny pro produkt:
  • 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
Klíčová slova: 
kbsqlsetup kbhowtomaster kbmt KB186133 KbMtcs
Strojově přeložený článek
DŮLEŽITÉ: Tento článek je přeložen pomocí softwaru na strojový překlad Microsoft. Nepřesný či chybný překlad lze opravit prostřednictvím technologie Community Translation Framework (CTF). Microsoft nabízí strojově přeložené, komunitou dodatečně upravované články, a články přeložené lidmi s cílem zajistit přístup ke všem článkům v naší znalostní bázi ve více jazycích. Strojově přeložené a dodatečně upravované články mohou obsahovat chyby ve slovníku, syntaxi a gramatice. Společnost Microsoft není odpovědná za jakékoliv nepřesnosti, chyby nebo škody způsobené nesprávným překladem obsahu nebo jeho použitím našimi zákazníky. Více o CTF naleznete na http://support.microsoft.com/gp/machine-translation-corrections/cs.
Projděte si také anglickou verzi článku: 186133

Dejte nám zpětnou vazbu

 

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