Artigo: 186133 - Última revisão: terça-feira, 14 de Fevereiro de 2006 - Revisão: 5.4

Como dinamicamente numerar linhas numa instrução Transact-SQL SELECT

Vista lado a ladoClique aqui para exibir o artigo traduzido e o artigo original em inglês, lado a lado.
Tradução automáticaVer isenção de responsabilidades para tradução automática
Ver produtos para os quais este artigo se aplica.
Dica do SistemaEste artigo aplica-se a um sistema operativo diferente do que está a utilizar. Foi desactivado o conteúdo do artigo, que pode não ser relevante para si.

Nesta página

Expandir tudo | Reduzir tudo

Sumário

Este artigo descreve como classificar dinamicamente linhas quando executa uma instrução SELECT utilizando um método flexível, que pode ser a solução só é possível e que é mais rápida do que a solução de procedimento. Linha numeração ou classificação é um problema de procedimento normal. As soluções baseiam-se normalmente no ciclos e tabelas temporárias; por conseguinte, baseiam-se do SQL Server ciclos e cursores. Esta técnica baseia-se uma associação automática. A relação seleccionada é normalmente "é maior que". Contar quantas vezes cada elemento de um determinado conjunto de dados tem a relação "é maior que" quando o conjunto é comparado a próprio.

Nota Os exemplos seguintes baseiam-se a base de dados pubs . Por predefinição, a base de dados de exemplo Adamastor e a dados de exemplo pubs não estão instalados no SQL Server 2005. Estas bases de dados podem ser transferidos a partir do Centro de transferências da Microsoft. Para mais informações, visite o seguinte Web site da Microsoft:
http://go.microsoft.com/fwlink/?linkid=30196 (http://go.microsoft.com/fwlink/?linkid=30196)
Depois de transferir SQL2000SampleDb.msi, extraia os scripts de base de dados de exemplo fazendo SQL2000SampleDb.msi. Por predefinição, SQL2000SampleDb.msi irá extrair os scripts de base de dados e um ficheiro Leiame para a seguinte pasta:
Bases de dados de C:\SQL Server 2000 exemplo
Siga as instruções no ficheiro readme para executar os scripts de instalação.
Voltar ao topo

Se estiver a utilizar o SQL Server 2005

Recomendamos que utilize funções de classificação que são fornecidas como uma nova funcionalidade do SQL Server 2005. Para mais informações sobre as funções de classificação, visite o seguinte Web site da Microsoft Developer Network (MSDN):
http://msdn2.microsoft.com/en-us/library/ms189798.aspx (http://msdn2.microsoft.com/en-us/library/ms189798.aspx)
Voltar ao topo

Exemplo 1

neste exemplo:
  • Conjunto de 1 é autores.
  • Conjunto de 2 é autores.
  • A relação é "primeiro e últimos nomes são superiores."
  • Pode evitar o problema duplicado comparando os nomes primeiro + últimos aos outros nomes primeiro + últimos.
  • Conte o número de vezes que a relação é satisfeita por count(*).
consulta : 
   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
utilizar o seguinte código no 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
resultado : 
   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)
Voltar ao topo

Exemplo 2

Neste exemplo:
  • Arquivos de posição pelo número de livros vendidos.
  • Conjunto de 1 é o número de livros vendidos por arquivo: seleccione id_loj, qty=sum(qty) do grupo de vendas por id_loj.
  • Conjunto de 2 é o número de livros vendidos por arquivo: seleccione id_loj, qty=sum(qty) do grupo de vendas por id_loj.
  • A relação é "o número de livros é maior que".
  • Para evitar duplicados, pode comparar (por exemplo) com preço * Qtd em vez de Qtd.
consulta : 
   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
resultado : 
   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: os valores na coluna Qtd. estão incorrectos. No entanto, a classificação dos arquivos consoante a quantidade de livros vendidos está correcta. Este é um erro deste método. Pode utilizar este método para devolver a classificação dos arquivos se não interessa a quantidade incorrecta no resultado.

Utilize o seguinte código no 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
resultado : 
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)
NOTA: em SQL Server 2005, pode receber o resultado da classificação e a quantidade correcto quando utilizar as funções de classificação.

Voltar ao topo

Exemplo 3

Neste exemplo:
  • Classificar os editores pelos lucros.
  • Conjunto de 1 é o total de vendas por fabricante: 
            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
  • Conjunto de 2 é o total de vendas por fabricante: 
            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
  • A relação é "ganha mais dinheiro do que".
consulta : 
   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
resultado : 
   Rank     Pub_Id   Sales
   ----     ------   --------
   1         0736    1,961.85
   2         0877    4,256.20
   3         1389    7,760.85

   (3 row(s) affected)
NOTA: os valores na coluna vendas estão incorrectos. No entanto, a classificação de fabricantes baseado os lucros está correcta.

Utilize o seguinte código no 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
resultado : 
rank     pub_id  sales
-------  ------  ---------
1        1389    2586.95
2        0877    2128.10
3        0736    1961.85

(3 row(s) affected)
Nota receberá o resultado correcto da classificação e o ganha quando utiliza as funções de classificação.

Voltar ao topo

Desvantagens

  • Devido a associação cruzada, este não foi concebido para trabalhar com um grande número de linhas. Funciona bem com centenas de linhas. No grandes tabelas, certifique-se utilizar um índice para evitar análises grandes.
  • Isto não funcionar bem com valores duplicados. Quando comparar valores duplicados, numeração de linhas descontínuas ocorre. Se não for este o comportamento que pretende, pode evitar-se ocultar a coluna classificação quando inserir o resultado numa folha de cálculo; utilize a folha de cálculo em vez disso, a numeração.

    Nota Se estiver a utilizar o SQL Server 2005, pode utilizar a função row_number() para devolver o número sequencial de uma linha, independentemente das linhas duplicadas.
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)
Voltar ao topo

Vantagens

  • Pode utilizar estas consultas em vistas e formatação do resultado.
  • Pode mudar os dados ordenados inferior mais à direita.
exemplo 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
resultado : 
   Publisher       Earnings
   -------------   --------
     0736          : 1,961.85
       0877        : 4,256.20
           1389    : 7,760.85
utilizar o seguinte código no 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
resultado : 
publisher            earnings
-------------------- ---------------------
  0736             : 1961.85
    0877           : 2128.10
        1389       : 2586.95

(3 row(s) affected)

exemplo 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
consulta : 
   select  Book=convert(varchar(45),replicate (' ', 2*rank) +
           title_id +
           replicate(' ', 35-2*rank)+': '),
           qty
   from v_title_rank
   order by rank
resultado : 
   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)
utilizar o seguinte código no 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
resultado : 
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)
Voltar ao topo
A informação contida neste artigo aplica-se a:
  • 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
Voltar ao topo
Palavras-chave: 
kbmt kbhowtomaster KB186133 KbMtpt
Voltar ao topo
Tradução automáticaTradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine translation ou MT), não tendo sido portanto revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática? erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 186133  (http://support.microsoft.com/kb/186133/en-us/ )
Voltar ao topo