Cómo numerar dinámicamente filas en una instrucción SELECT de Transact-SQL

Seleccione idioma Seleccione idioma
Id. de artículo: 186133 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

Resumen

En este artículo se describe cómo clasificar dinámicamente filas cuando realiza una instrucción SELECT mediante un método flexible que puede ser la única solución posible y que es más rápido que la solución del procedimiento. La numeración u ordenación de filas es un problema de procedimiento bastante habitual. Las soluciones normalmente se basan en bucles y tablas temporales; por lo tanto, se basan en SQL Server bucles y cursores. Esta técnica se basa en una instrucción AUTO JOIN. La relación elegida es normalmente "es mayor que". Cuenta cuántas veces cada elemento de un conjunto de datos determinado cumple la relación "es mayor que" cuando se compara el conjunto a sí mismo.

Nota Los siguientes ejemplos se basan en la base de datos pubs . De forma predeterminada, la base de datos Neptuno y la base de datos de ejemplo pubs no están instalados en SQL Server 2005. Estas bases de datos pueden descargarse desde el Centro de descarga de Microsoft. Para obtener más información al respecto, visite el siguiente sitio Web de Microsoft:
http://go.microsoft.com/fwlink/?linkid=30196
Después de descargar SQL2000SampleDb.msi, extraer las secuencias de comandos de base de datos de ejemplo haciendo doble clic en SQL2000SampleDb.msi. De forma predeterminada, SQL2000SampleDb.msi extraerá las secuencias de comandos de base de datos y un archivo Léame en la siguiente carpeta:
Bases de datos de ejemplo C:\SQL Server 2000
Siga las instrucciones en el archivo Léame para ejecutar las secuencias de comandos de instalación.

Si está utilizando SQL Server 2005

Recomendamos que utilice funciones de clasificación que se proporcionan como una característica nueva en SQL Server 2005. Para obtener más información sobre las funciones de clasificación, visite el siguiente sitio Web de Microsoft Developer Network (MSDN):
http://msdn2.microsoft.com/en-us/library/ms189798.aspx

Ejemplo 1

en este ejemplo:
  • El conjunto 1 es <i>authors</i>.
  • Conjunto 2 es authors.
  • La relación es "el nombre y el apellido son mayores que"
  • Puede evitar el problema duplicado comparando los nombres de los demás nombres primero + últimos primero + últimos.
  • Contar el número de veces que se cumple la relación 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
				
utilice el código siguiente en 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)
				

Ejemplo 2

En este ejemplo:
  • Clasificar almacenes por el número de libros vendidos.
  • El conjunto 1 es el número de libros vendidos por almacén: seleccione stor_id, qty=sum(qty) de grupo de venta por stor_id.
  • Conjunto 2 es el número de libros vendidos por almacén: seleccione stor_id, qty=sum(qty) de grupo de venta por stor_id.
  • La relación es "el número de libros es mayor que".
  • Para evitar duplicados, puede (como un ejemplo) comparar precio * cantidad en lugar de cantidad
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 los valores en la columna Qty son incorrectos. Sin embargo, la clasificación de almacenes según la cantidad de libros vendidos es correcta. Se trata de un defecto de este método. Puede utilizar este método para devolver la clasificación de almacenes si no le importa sobre la cantidad incorrecta en el resultado.

Utilice el código siguiente en 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 en SQL Server 2005, puede recibir el resultado correcto de la clasificación y la cantidad cuando utilice las funciones de clasificación.

Ejemplo 3

En este ejemplo:
  • Clasificar los editores por sus beneficios.
  • Conjunto 1 es el total de ventas por publisher:
            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 es el total de ventas por publisher:
            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 relación es "gana más dinero 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 los valores en la columna ventas son incorrectos. Sin embargo, la clasificación de editores basándose en los beneficios de es correcta.

Utilice el código siguiente en 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 recibe el resultado correcto de la clasificación y la ganancia cuando utilice las funciones de clasificación.

Inconvenientes

  • Debido a de la combinación cruzada, esto no está diseñado para trabajar con un gran número de filas. Funciona bien para cientos de filas. En tablas grandes, asegúrese de utilizar un índice para evitar exploraciones grandes.
  • No funciona bien si hay valores duplicados. Numeración de fila discontinuo se produce cuando se comparan valores duplicados. Si esto no es el comportamiento que desea, puede evitar si ocultar la columna rango cuando inserta el resultado en una hoja de cálculo; utilice la hoja de cálculo numeración en su lugar.

    Nota Si utiliza SQL Server 2005, puede utilizar la función row_number() para devolver el número secuencial de una fila, independientemente de las filas 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)
				

Ventajas

  • Puede utilizar estas consultas en las vistas y formato de resultado.
  • Puede cambiar los datos ordenados inferior más a la derecha.
ejemplo 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
				
utilice el código siguiente en 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)

ejemplo 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)
				
utilice el código siguiente en 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)

 

Propiedades

Id. de artículo: 186133 - Última revisión: martes, 14 de febrero de 2006 - Versión: 5.4
La información de este artículo se refiere 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 Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Palabras clave: 
kbmt kbhowtomaster KB186133 KbMtes
Traducción automática
IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft (http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes. Microsoft suele actualizar el software de traducción frecuentemente.
Haga clic aquí para ver el artículo original (en inglés): 186133

Enviar comentarios

 

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