Requête SQLServer SQL avec un compteur de lignes
j'ai une requête SQL qui retourne un ensemble de lignes:
SELECT id, name FROM users where group = 2
j'ai besoin d'inclure également une colonne qui a une incrémentation valeur d'entier, de sorte que la première ligne doit avoir un 1 dans la colonne compteur, le deuxième 2 points, le troisième 3, etc
la requête montrée ici n'est qu'un exemple simplifié, en réalité la requête pourrait être arbitrairement complexe, avec plusieurs jointures et requêtes imbriquées.
je sais que cela pourrait être réalisé en utilisant une table temporaire avec un numéro d'auto champ, mais est-il un moyen de le faire dans la requête elle-même ?
4 réponses
Pour commencer, quelque chose le long des lignes de:
SELECT my_first_column, my_second_column,
ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
FROM my_table
cependant, il est important de noter que le ROW_NUMBER() OVER (ORDER BY ...) la construction ne détermine que les valeurs de Row_Counter, il ne garantit pas l'ordre des résultats.
sauf si le SELECT lui-même a une explicite ORDER BY clause, les résultats peuvent être retournés dans n'importe quel ordre, dépendant de la façon dont SQL Server décide d'optimiser la requête. (Voir cet article pour plus d'info.)
le seul moyen afin de garantir que les résultats toujours à retourner dans Row_Counter l'ordre doit appliquer exactement le même ordre aux deux SELECT et ROW_NUMBER():
SELECT my_first_column, my_second_column,
ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
FROM my_table
ORDER BY my_order_column -- exact copy of the ordering used for Row_Counter
le modèle ci-dessus retournera toujours les résultats dans le bon ordre et fonctionne bien pour les requêtes simples, mais qu'en est-il d'une requête "arbitrairement complexe" avec peut-être des dizaines d'expressions dans le ORDER BY l'article? Dans ces situations, je préfère quelque chose comme ceci:
SELECT t.*
FROM
(
SELECT my_first_column, my_second_column,
ROW_NUMBER() OVER (ORDER BY ...) AS Row_Counter -- complex ordering
FROM my_table
) AS t
ORDER BY t.Row_Counter
à l'Aide d'un requête imbriquée signifie qu'il n'y a pas besoin de dupliquer l'compliqué ORDER BY clause, ce qui signifie moins d'encombrement et un entretien plus facile. L'extérieur ORDER BY t.Row_Counter rend également l'intention de la requête beaucoup plus claire pour vos collègues développeurs.
Dans SQL Server 2005, vous pouvez utiliser le ROW_NUMBER() fonction, qui a des options pour l'ordre de tri et les groupes sur lesquels les comptes sont faits (et réinitialisés).
la façon la plus simple est d'utiliser un compteur de lignes variable. Cependant ce serait deux commandes SQL réelles. L'un pour définir la variable, puis de la requête comme suit:
SET @n=0;
SELECT @n:=@n+1, a.* FROM tablename a
Votre requête peut être aussi complexe que vous le souhaitez avec des jointures etc. J'ai l'habitude de faire cela d'une procédure stockée. Vous pouvez avoir toutes sortes de plaisir avec la variable, même l'utiliser pour calculer l'encontre des valeurs de champ. La clé est le :=
Heres une approche différente. Si vous avez plusieurs tables de données qui ne sont pas joignable, ou pour une raison, vous ne voulez pas compter toutes les lignes en même temps, mais vous voulez continuer à faire partie du même nombre de lignes, vous pouvez créer une table qui fait le travail pour vous.
Exemple:
create table #test (
rowcounter int identity,
invoicenumber varchar(30)
)
insert into #test(invoicenumber) select [column] from [Table1]
insert into #test(invoicenumber) select [column] from [Table2]
insert into #test(invoicenumber) select [column] from [Table3]
select * from #test
drop table #test