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

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
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 :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.

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

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

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.qtyfrom (select stor_id, qty=sum(qty) from sales group by stor_id) as s1
les résultats de :
rank     stor_id  qty-------  -------  ------1        7131     1302        7066     1253        7067     904        8042     805        7896     606        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.

back to the top

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

back to the top

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

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_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
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_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
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) 
back to the top
requête de mise en forme rang de la jointure prodsql

Avertissement : Cet article a été traduit automatiquement.

Propriétés

ID d'article : 186133 - Dernière mise à jour : 02/14/2006 08:04:53 - Révision : 5.4

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

  • kbmt kbhowtomaster KB186133 KbMtfr
Commentaires