Você está offline; aguardando reconexão
Entrar

Não há suporte para seu navegador

Você precisa atualizar seu navegador para usar o site.

Atualize para a versão mais recente do Internet Explorer

Como Numerar linhas dinamicamente em uma instrução Transact-SQL SELECT

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 traduzido ou revisto por pessoas. A Microsoft possui artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais, com o objetivo de oferecer em português a totalidade dos artigos existentes na base de dados de suporte. No entanto, a tradução automática não é sempre perfeita, podendo conter erros de vocabulário, sintaxe ou gramática. A Microsoft não é responsável por incoerências, erros ou prejuízos ocorridos em decorrência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza atualizações freqüentes ao software de tradução automática (MT). Obrigado.

Clique aqui para ver a versão em Inglês deste artigo: 186133
Sumário
Este artigo descreve como classificar linhas dinamicamente, quando você executa uma instrução SELECT usando um método flexível, que pode ser a solução só é possível e que é mais rápido do que a solução de procedimento. Linha numeração ou classificação é uma questão procedimento típica. As soluções são geralmente baseadas no loops e tabelas temporárias; portanto, eles são baseados no SQL Server loops e cursores. Essa técnica é baseada em uma associação automática. A relação escolhida é geralmente "é maior que". Contagem de quantas vezes cada elemento de um determinado conjunto de dados preenche a relação "é maior que" quando o conjunto é comparado a mesmo.

Observação Os exemplos a seguir se baseiam no banco de dados pubs . Por padrão, o banco de dados de exemplo Northwind e banco de dados de exemplo pubs não estiverem instalado no SQL Server 2005. Esses bancos de dados podem ser baixados do Centro de download da Microsoft. Para obter mais informações, visite o seguinte site:Depois de fazer o download SQL2000SampleDb.msi, extraia os scripts de banco de dados de exemplo clicando duas vezes em SQL2000SampleDb.msi. Por padrão, SQL2000SampleDb.msi extrairá os scripts de banco de dados e um arquivo Leiame na seguinte pasta:
C:\SQL Server 2000 Sample Databases
Siga as instruções no arquivo Leiame para executar os scripts de instalação.

Se você estiver usando o SQL Server 2005

Recomendamos que você use funções de classificação que são fornecidas como um novo recurso do SQL Server 2005. Para obter mais informações sobre as funções de classificação, visite o seguinte site da Web Microsoft Developer Network (MSDN):back to the top

Exemplo 1

neste exemplo:
  • Conjunto 1 é autores.
  • Conjunto 2 é autores.
  • A relação é "Sobrenome e o nome é maior que".
  • Você pode evitar o problema duplicado comparando os nomes primeiro + últimos para os outros nomes primeiro + últimos.
  • Conte o número de vezes que a relação é atendida 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				
usar o código a seguir 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)				
back to the top

Exemplo 2

Neste exemplo:
  • Classificar armazenamentos pelo número de livros vendidos.
  • Conjunto 1 é o número de livros vendidos pela loja: selecione stor_id, qty=sum(qty) de grupo de vendas por stor_id.
  • Conjunto 2 é o número de livros vendidos pela loja: selecione stor_id, qty=sum(qty) de grupo de vendas por stor_id.
  • A relação é "o número de livros é maior que".
  • Para evitar duplicatas, você pode (como um exemplo) comparar preço * quantidade 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)				
Observação os valores na coluna Qtd são incorretos. No entanto, a classificação dos armazenamentos com base na quantidade de livros vendidos está correta. Este é um defeito desse método. Você pode usar esse método para retornar a classificação dos armazenamentos se você não faz sobre a quantidade de errado no resultado.

Use o seguinte código no 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
resultado :
rank     stor_id  qty-------  -------  ------1        7131     1302        7066     1253        7067     904        8042     805        7896     606        6380     8(6 row(s) affected)
anotação SQL Server 2005, você pode receber o resultado correto da classificação e a quantidade ao usar as funções de classificação.

back to the top

Exemplo 3

Neste exemplo:
  • Classificar os editores por seus ganhos.
  • Conjunto 1 é o total de vendas por editora:
            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 2 é o total de vendas por editora:
            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 é "Render 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)				
Observação os valores na coluna vendas são incorretos. No entanto, a classificação de editores com base nos ganhos está correta.

Use o código a seguir 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.952        0877    2128.103        0736    1961.85(3 row(s) affected)				
Observação você recebe o resultado correto da classificação e ganhando quando você usa as funções de classificação.

back to the top

Desvantagens

  • Devido a associação entre, isso não foi projetado para trabalhar com um grande número de linhas. Ele funciona bem para centenas de linhas. Em tabelas grandes, certifique-se usar um índice para evitar verificações grandes.
  • Isso não funcionar bem com valores duplicados. Quando você compara valores duplicados, numeração de linha descontínuo ocorre. Se não for esse o comportamento que você deseja, você pode evitá-lo ocultar a coluna de classificação quando você insere o resultado em uma planilha; use a planilha numeração em vez disso.

    Observação Se você estiver usando o SQL Server 2005, você pode usar a função row_number() para retornar o número seqüencial de uma linha, independentemente de 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)				
back to the top

Benefícios

  • Você pode usar essas consultas em modos de exibição e formatação de resultado.
  • Você pode alternar os dados classificados 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				
consulta :
   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				
usar o código a seguir no 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
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)				
usar o código a seguir no 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
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) 
back to the top
prodsql associação classificação formatação consulta

Aviso: este artigo foi traduzido automaticamente

Propriedades

ID do Artigo: 186133 - Última Revisão: 02/14/2006 08:04:53 - Revisão: 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 KbMtpt
Comentários
y>ow.location.protocol) + "//c.microsoft.com/ms.js'><\/script>");