Al momento sei offline in attesa che la connessione Internet venga ristabilita

Come numero in modo dinamico righe in un'istruzione Transact-SQL SELECT

Il presente articolo è stato tradotto tramite il software di traduzione automatica di Microsoft e non da una persona. Microsoft offre sia articoli tradotti da persone fisiche sia articoli tradotti automaticamente da un software, in modo da rendere disponibili tutti gli articoli presenti nella nostra Knowledge Base nella lingua madre dell’utente. Tuttavia, un articolo tradotto in modo automatico non è sempre perfetto. Potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli, più o meno allo stesso modo di come una persona straniera potrebbe commettere degli errori parlando una lingua che non è la sua. Microsoft non è responsabile di alcuna imprecisione, errore o danno cagionato da qualsiasi traduzione non corretta dei contenuti o dell’utilizzo degli stessi fatto dai propri clienti. Microsoft, inoltre, aggiorna frequentemente il software di traduzione automatica.

Clicca qui per visualizzare la versione originale in inglese dell’articolo: 186133
Sommario
In questo articolo viene descritto come classificare in modo dinamico righe quando si esegue un'istruzione SELECT utilizzando un metodo flessibile, che può essere la soluzione possibile solo e che è più la soluzione procedura. Riga di numerazione o di valutazione è un tipico problema procedura. Le soluzioni in genere basate su cicli e le tabelle temporanee; pertanto, sono basati su SQL Server cicli e cursori. Questa tecnica si basa su un join automatico. La relazione selezionata è in genere "è maggiore." Conteggio quante volte ogni elemento di un particolare set di dati soddisfa la relazione "è maggiore di" quando viene confrontato con l'insieme a se stesso.

Nota Negli esempi riportati di seguito si basano sul database pubs . Per impostazione predefinita, al database di esempio di Northwind e il database di esempio pubs non vengono installati in SQL Server 2005. Questi database è possibile scaricare dall'area Download Microsoft. Per ulteriori informazioni, visitare il sito di Web di Microsoft:Dopo avere scaricato SQL2000SampleDb.msi, è possibile estrarre gli script di database di esempio facendo doppio clic su SQL2000SampleDb.msi. Per impostazione predefinita, SQL2000SampleDb.msi estrarrà gli script di database e un file readme nella seguente cartella:
Database di esempio C:\SQL Server 2000
Seguire le istruzioni nel file readme di eseguire gli script di installazione.

Se si utilizza SQL Server 2005

Si consiglia di utilizzare le funzioni di classificazione sono fornite come una nuova funzionalità in SQL Server 2005. Per ulteriori informazioni sulle funzioni di classificazione, visitare il seguente sito Web MSDN (informazioni in lingua inglese):back to the top

Esempio 1

in questo esempio:
  • Set 1 è gli autori.
  • Set di 2 è gli autori.
  • La relazione è "cognomi e i nomi sono maggiori."
  • È possibile evitare il problema duplicato confrontando i nomi primo + ultimi gli altri nomi primo + l'ultimo.
  • Contare il numero di volte che la relazione viene soddisfatta da count(*).
query :
   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				
utilizzare il codice riportato di seguito in 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 
risultato :
   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

Esempio 2

In questo esempio:
  • Classificare gli archivi del numero di libri venduti.
  • 1 È il numero di libri venduti dall'archivio: selezionare stor_id, qty=sum(qty) dal gruppo di vendite da stor_id.
  • Insieme di 2 è il numero di libri venduti dall'archivio: selezionare stor_id, qty=sum(qty) dal gruppo di vendite da stor_id.
  • La relazione è la "il numero di libri è maggiore."
  • Per evitare duplicati, è possibile (ad esempio) confrontare prezzo * Qtà anziché Qtà
query :
   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				
risultato :
   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)				
Nota I valori nella colonna Qty sono errati. Tuttavia, la valutazione degli archivi in base alla quantità di libri venduti è corretta. Si tratta di un difetto di questo metodo. È possibile utilizzare questo metodo per restituire la valutazione degli archivi, se non esistono esigenze particolari sulla quantità errata nel risultato.

Utilizzare il codice seguente in 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
risultato :
rank     stor_id  qty-------  -------  ------1        7131     1302        7066     1253        7067     904        8042     805        7896     606        6380     8(6 row(s) affected)
Nota In SQL Server 2005, è possibile ricevere il risultato corretto della valutazione e la quantità quando è utilizzare le funzioni classificazione.

back to the top

Esempio 3

In questo esempio:
  • Consente di classificare gli editori per i profitti.
  • Set 1 è i totale delle vendite in base all'editore:
            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 È i totale delle vendite in base all'editore:
            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					
  • La relazione è "guadagna più fondi rispetto a".
query :
   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				
risultato :
   Rank     Pub_Id   Sales   ----     ------   --------   1         0736    1,961.85   2         0877    4,256.20   3         1389    7,760.85   (3 row(s) affected)				
Nota I valori nella colonna Vendite sono errati. Tuttavia, la valutazione degli editori negli utili di base è corretta.

Utilizzare il codice seguente in 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
risultato :
rank     pub_id  sales-------  ------  ---------1        1389    2586.952        0877    2128.103        0736    1961.85(3 row(s) affected)				
Nota È visualizzato il risultato corretto della valutazione e frutta quando si utilizzano le funzioni di classificazione.

back to the top

Svantaggi

  • Causa di un cross join, questo non è progettato per l'utilizzo di un numero elevato di righe. Funziona anche per centinaia di righe. Nelle tabelle di grandi dimensioni, assicurarsi di utilizzare un indice per evitare l'analisi di grandi dimensioni.
  • Il problema persiste anche con valori duplicati. Quando si confrontano valori duplicati, numerazione di riga discontinuo si verifica. Se non si tratta il comportamento che si desidera, è possibile evitare nascondere la colonna d'ordine quando si inserisce il risultato in un foglio di calcolo, utilizzare il foglio di calcolo invece di numerazione.

    Nota Se si utilizza SQL Server 2005, è possibile utilizzare la funzione di row_number() per restituire il numero sequenza di una riga, indipendentemente le righe duplicate.
Example:
   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
Result:
   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

Vantaggi

  • È possibile utilizzare le query nelle visualizzazioni e formattazione del risultato.
  • È possibile spostare i dati classificati in inferiore più a destra.
esempio 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				
query :
   select  publisher=convert(varchar(20),replicate (' ', power(2,rank)) +           pub_id +           replicate(' ', 15-power(2,rank))+': '),           earnings=qty   from v_pub_rank				
risultato :
   Publisher       Earnings   -------------   --------     0736          : 1,961.85       0877        : 4,256.20           1389    : 7,760.85				
utilizzare il codice riportato di seguito in 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
risultato :
publisher            earnings-------------------- ---------------------  0736             : 1961.85    0877           : 2128.10        1389       : 2586.95(3 row(s) affected)

ad esempio :
   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				
query :
   select  Book=convert(varchar(45),replicate (' ', 2*rank) +           title_id +           replicate(' ', 35-2*rank)+': '),           qty   from v_title_rank   order by rank				
risultato :
   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)				
utilizzare il codice riportato di seguito in 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
risultato :
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
query formattazione rango join prodsql

Avviso: questo articolo è stato tradotto automaticamente

Proprietà

ID articolo: 186133 - Ultima revisione: 02/14/2006 08:04:53 - Revisione: 5.4

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

  • kbmt kbhowtomaster KB186133 KbMtit
Feedback
&t=">