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

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
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: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):back to the top

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)				
back to the top

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

back to the top

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.952        0877    2128.103        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.

back to the top

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)				
back to the top

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_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)

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_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
consulta de formato de rango de combinación de prodsql

Advertencia: este artículo se tradujo automáticamente

Propiedades

Id. de artículo: 186133 - Última revisión: 02/14/2006 08:04:53 - Revisión: 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 KbMtes
Comentarios