Numéro d'article: 186133 - Dernière mise à jour: mardi 14 février 2006 - Version: 5.4

Comment numéroter dynamiquement des lignes dans une instruction SELECT Transact-SQL

Affichage côte à côteCliquez ici si vous souhaitez afficher en côte à côte l?article anglais et sa traduction automatique en français
Traduction automatiqueATTENTION : Cet article est issu du système de traduction automatique
Voir les produits auxquels s'applique cet article
A noterCet article s'applique à un système d'exploitation différent de celui que vous utilisez. Le contenu de l'article qui ne vous concerne peut-être pas est désactivé.

Sommaire

Agrandir tout | Réduire tout

Résumé

Cet article décrit comment classer dynamiquement les lignes lorsque vous effectuez une instruction SELECT à l'aide d'une méthode souple, qui peut être la seule solution possible et qui est plus rapide que la solution procédurale. Ligne numérotation ou de classement est un problème des procédures standard. Les solutions sont généralement basées sur les boucles et les tables temporaires ; par conséquent, ils sont basés sur SQL Server boucles et les curseurs. Cette technique est basée sur une jointure automatique. La relation sélectionnée est généralement «est supérieur à». Count Nombre de fois où chaque élément d'un ensemble particulier de données répond à la relation «est supérieur à» quand le jeu est comparé à elle-même.

Remarque Les exemples suivants reposent sur la base de données pubs. Par défaut, la base de données exemple les Comptoirs et la base de données exemple pubs ne sont pas installés dans SQL Server 2005. Ces bases de données peuvent être téléchargés à partir du centre de téléchargement Microsoft. Pour plus d'informations, reportez-vous au site Web de Microsoft à l'adresse suivante :
http://go.microsoft.com/fwlink/?linkid=30196 (http://go.microsoft.com/fwlink/?linkid=30196)
Après avoir téléchargé SQL2000SampleDb.msi, extraire les exemples de scripts de base de données en double-cliquant sur SQL2000SampleDb.msi. Par défaut, SQL2000SampleDb.msi extrait les scripts de base de données et un fichier readme dans le dossier suivant :
Bases de données exemple C:\SQL Server 2000
Suivez les instructions dans le fichier Lisezmoi pour exécuter les scripts d'installation.
Retour au début

Si vous utilisez SQL Server 2005

Nous vous recommandons d'utiliser les fonctions de classement sont fournies en tant que nouvelle fonctionnalité dans SQL Server 2005. Pour plus d'informations sur les fonctions de classement, reportez-vous au site Web de MSDN (Microsoft Developer Network) à l'adresse suivante :
http://msdn2.microsoft.com/en-us/library/ms189798.aspx (http://msdn2.microsoft.com/en-us/library/ms189798.aspx)
Retour au début

Exemple 1

Dans cet exemple :
  • Jeu 1 est auteurs.
  • Ensemble 2 est auteurs.
  • La relation est «noms et prénoms figurant est supérieur à».
  • Vous pouvez éviter le problème en double en comparant les noms de première + derniers et les autres premier + dernier.
  • Compter le nombre de fois que la relation est remplie par count(*).
Requête : 
   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
utiliser le code suivant dans 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
result : 
   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)
Retour au début

Exemple 2

Dans cet exemple :
  • Classez banques par le nombre de livres vendus.
  • Jeu 1 est le nombre de livres vendus par magasin : sélectionnez stor_id, qty=sum(qty) du groupe vente par stor_id.
  • Ensemble 2 est le nombre de livres vendus par magasin : sélectionnez stor_id, qty=sum(qty) du groupe vente par stor_id.
  • La relation est «le nombre de livres est supérieur à».
  • Pour éviter les doublons, vous pouvez (par exemple) comparer prix * Qté au lieu de Qté
Requête : 
   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
result : 
   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)
note les valeurs dans la colonne Qty sont incorrectes. Toutefois, le classement des magasins en fonction de la quantité de livres vendus est correct. Il s'agit d'un défaut de cette méthode. Cette méthode permet de renvoyer le classement des banques si vous ne souhaitez pas sur la quantité erronée dans le résultat.

Utilisez le code suivant dans 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
les résultats de : 
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)
note dans SQL Server 2005, vous pouvez recevoir le résultat correct de la priorité et la quantité lorsque vous utilisez les fonctions de classement.

Retour au début

Exemple 3

Dans cet exemple :
  • Classez les éditeurs par leurs recettes.
  • Jeu 1 est le total des ventes par éditeur : 
            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
  • Ensemble 2 est le total des ventes par éditeur : 
            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 relation est «gagne plus d'argent que».
Requête : 
   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
result : 
   Rank     Pub_Id   Sales
   ----     ------   --------
   1         0736    1,961.85
   2         0877    4,256.20
   3         1389    7,760.85

   (3 row(s) affected)
note les valeurs dans la colonne ventes sont incorrectes. Toutefois, le classement des éditeurs basés sur les salaires est correct.

Utilisez le code suivant dans 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
Résultat : 
rank     pub_id  sales
-------  ------  ---------
1        1389    2586.95
2        0877    2128.10
3        0736    1961.85

(3 row(s) affected)
note, vous obtenez le résultat correct du rang et la gagner lorsque vous utilisez les fonctions de classement.

Retour au début

Inconvénients

  • En raison de la jointure croisée, cela n'est pas conçu pour travailler avec un grand nombre de lignes. Il fonctionne bien pour des centaines de lignes. Sur des tables de grande taille, assurez-vous d'utiliser un index pour éviter les analyses de grande taille.
  • Cela ne fonctionne pas correctement avec des valeurs en double. Lorsque vous comparez des valeurs en double, ligne discontinue numérotation se produit. Si ce n'est pas le comportement que vous souhaitez, vous pouvez l'éviter en masquant la colonne rangée lorsque vous insérez le résultat dans une feuille de calcul ; Utilisez la feuille de calcul numérotation à la place.

    Remarque Si vous utilisez SQL Server 2005, vous pouvez utiliser la fonction row_number() pour renvoyer le numéro séquentiel d'une ligne, sans tenir compte des lignes en double.
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)
Retour au début

Avantages

  • Vous pouvez utiliser ces requêtes dans les vues et les résultats de la mise en forme.
  • Vous pouvez déplacer les données rangée inférieure plus à droite.
Exemple 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
result : 
   Publisher       Earnings
   -------------   --------
     0736          : 1,961.85
       0877        : 4,256.20
           1389    : 7,760.85
utiliser le code suivant dans 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
result : 
publisher            earnings
-------------------- ---------------------
  0736             : 1961.85
    0877           : 2128.10
        1389       : 2586.95

(3 row(s) affected)

Exemple 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
Query : 
   select  Book=convert(varchar(45),replicate (' ', 2*rank) +
           title_id +
           replicate(' ', 35-2*rank)+': '),
           qty
   from v_title_rank
   order by rank
result : 
   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)
utiliser le code suivant dans 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
result : 
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)
Retour au début
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 4.21a Standard
  • Microsoft SQL Server 6.0 Standard
  • Microsoft SQL Server 6.5 Édition Standard
  • Microsoft SQL Server 7.0 Standard
  • 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
Retour au début
Mots-clés : 
kbmt kbhowtomaster KB186133 KbMtfr
Retour au début
Traduction automatiqueTraduction automatique
IMPORTANT : Cet article est issu du système de traduction automatique mis au point par Microsoft (http://support.microsoft.com/gp/mtdetails). Un certain nombre d?articles obtenus par traduction automatique sont en effet mis à votre disposition en complément des articles traduits en langue française par des traducteurs professionnels. Cela vous permet d?avoir accès, dans votre propre langue, à l?ensemble des articles de la base de connaissances rédigés originellement en langue anglaise. Les articles traduits automatiquement ne sont pas toujours parfaits et peuvent comporter des erreurs de vocabulaire, de syntaxe ou de grammaire (probablement semblables aux erreurs que ferait une personne étrangère s?exprimant dans votre langue !). Néanmoins, mis à part ces imperfections, ces articles devraient suffire à vous orienter et à vous aider à résoudre votre problème. Microsoft s?efforce aussi continuellement de faire évoluer son système de traduction automatique.
La version anglaise de cet article est la suivante: 186133  (http://support.microsoft.com/kb/186133/en-us/ )
Retour au début
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.