Afficher tous les loggings RowNumber pour les duplicates?

J'ai une table de ressources de string:

;WITH cte AS ( SELECT 1 AS id , 'john' AS name, 10 AS age UNION SELECT 2 AS id , 'john' AS name, 10 AS age UNION SELECT 3 AS id , 'john' AS name, 12 AS age UNION SELECT 4 AS id , 'paul' AS name, 6 AS age UNION SELECT 5 AS id , 'paul ' AS name, 6 AS age UNION SELECT 6 AS id , 'paul different' AS name, 7 AS age UNION SELECT 7 AS id , 'ringo' AS name, 2 AS age )

Donc, le nom "John" a l'âge de 10 ans.

Plus tard, quelqu'un d'autre (pas moi) a ajouté "john" avec 10 ans.

Donc, je veux nettoyer tous les duplicates.

Mais ce n'est pas le problème. Avant de supprimer, je veux voir tous les duplicates.

Alors j'ai fait ceci:

 SELECT * FROM ( SELECT ID, name, age, ROW_NUMBER() OVER(PARTITION BY name, age ORDER BY id) AS rn FROM cte ) a WHERE a.rn>1 ORDER BY name, age, a.rn

Résultat :

entrez la description de l'image ici

Ce qui me montre essentiellement des duplicates. Mais je veux voir aussi où rn=1 seulement s'il y a plus de version pour la valeur courante.

Question

En d'autres termes: Comment puis-je améliorer ma requête afin:

Afficher toutes les versions pour un logging (tous les numéros de ligne, rn ) uniquement s'il existe des versions pour cet logging

Résultat désiré :

 ID name age rn 1 john 10 1 2 john 10 2 4 paul 6 1 5 paul 6 2

Sql en ligne – démo

NB Je sais que je peux le faire avec une nouvelle parsing de la table pour le même name et l' age . Je pensais que s'il y avait une façon plus élégante de le faire.

Utiliser l'opérateur exists pour find le nom qui sont duplicated . Essaye ça.

 ;WITH cte AS ( SELECT 1 AS id , 'john' AS name, 10 AS age UNION SELECT 2 AS id , 'john' AS name, 10 AS age UNION SELECT 3 AS id , 'john' AS name, 12 AS age UNION SELECT 4 AS id , 'paul' AS name, 6 AS age UNION SELECT 5 AS id , 'paul ' AS name, 6 AS age UNION SELECT 6 AS id , 'paul different' AS name, 7 AS age UNION SELECT 7 AS id , 'ringo' AS name, 2 AS age ) , cte1 AS (SELECT ID, name, age, Row_number() OVER(PARTITION BY name, age ORDER BY id) AS rn FROM cte) SELECT * FROM cte1 a WHERE EXISTS (SELECT 1 FROM cte1 b WHERE a.name = b.name and a.age=b.age AND b.rn > 1) ORDER BY name, age, a.rn

ou utiliser Inner Join

  SELECT a.id,a.name,a.age FROM cte1 a JOIN cte1 b ON a.name = b.name AND a.age = b.age AND b.rn > 1 ORDER BY a.name, a.age, a.rn

Ou Pour le faire dans une parsing de table unique, utilisez Dense_Rank plus la window function

  ;WITH cte AS ( SELECT 1 AS id , 'john' AS name, 10 AS age UNION SELECT 2 AS id , 'john' AS name, 10 AS age UNION SELECT 3 AS id , 'john' AS name, 12 AS age UNION SELECT 4 AS id , 'paul' AS name, 6 AS age UNION SELECT 5 AS id , 'paul ' AS name, 6 AS age UNION SELECT 6 AS id , 'paul different' AS name, 7 AS age UNION SELECT 7 AS id , 'ringo' AS name, 2 AS age ) , cte1 AS (SELECT ID, name, age, count(age) over (partition by name,age) cnt, dense_rank() OVER(PARTITION BY name ORDER BY age) AS rn FROM cte) SELECT * FROM cte1 WHERE rn = 1 AND cnt > 1