Trouver des valeurs doubles dans une table SQL

Mots clés : sqlduplicatessql

meilleur 5 Réponses Trouver des valeurs doubles dans une table SQL

vote vote

98

SELECT     name, email, COUNT(*) FROM     users GROUP BY     name, email HAVING      COUNT(*) > 1 
vote vote

83

declare @YourTable table (id int, name varchar(10), email varchar(50))  INSERT @YourTable VALUES (1,'John','John-email') INSERT @YourTable VALUES (2,'John','John-email') INSERT @YourTable VALUES (3,'fred','John-email') INSERT @YourTable VALUES (4,'fred','fred-email') INSERT @YourTable VALUES (5,'sam','sam-email') INSERT @YourTable VALUES (6,'sam','sam-email')  SELECT     name,email, COUNT(*) AS CountOf     FROM @YourTable     GROUP BY name,email     HAVING COUNT(*)>1 
name       email       CountOf ---------- ----------- ----------- John       John-email  2 sam        sam-email   2  (2 row(s) affected) 
SELECT     y.id,y.name,y.email     FROM @YourTable y         INNER JOIN (SELECT                         name,email, COUNT(*) AS CountOf                         FROM @YourTable                         GROUP BY name,email                         HAVING COUNT(*)>1                     ) dt ON y.name=dt.name AND y.email=dt.email 
id          name       email ----------- ---------- ------------ 1           John       John-email 2           John       John-email 5           sam        sam-email 6           sam        sam-email  (4 row(s) affected) 
DELETE d     FROM @YourTable d         INNER JOIN (SELECT                         y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank                         FROM @YourTable y                             INNER JOIN (SELECT                                             name,email, COUNT(*) AS CountOf                                             FROM @YourTable                                             GROUP BY name,email                                             HAVING COUNT(*)>1                                         ) dt ON y.name=dt.name AND y.email=dt.email                    ) dt2 ON d.id=dt2.id         WHERE dt2.RowRank!=1 SELECT * FROM @YourTable 
id          name       email ----------- ---------- -------------- 1           John       John-email 3           fred       John-email 4           fred       fred-email 5           sam        sam-email  (4 row(s) affected) 
vote vote

72

SELECT name, email FROM users GROUP BY name, email HAVING ( COUNT(*) > 1 ) 
vote vote

65

SELECT id, name, email  FROM users u, users u2 WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id 
DELETE FROM users WHERE id IN (     SELECT id/*, name, email*/     FROM users u, users u2     WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id ) 
vote vote

52

SELECT  * FROM    (  SELECT a.*  ,      Row_Number() OVER (PARTITION BY Name, Age ORDER BY Name) AS r  FROM   Customers AS a )       AS b WHERE   r > 1; 
CREATE TABLE test (         id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY ,       c1      integer ,       c2      text ,       d       date DEFAULT now() ,       v       text );  INSERT INTO test (c1, c2, v) VALUES (1, 'a', 'Select'), (1, 'a', 'ALL'), (1, 'a', 'multiple'), (1, 'a', 'records'), (2, 'b', 'in columns'), (2, 'b', 'c1 and c2'), (3, 'c', '.'); SELECT * FROM test ORDER BY 1;  SELECT  * FROM    test WHERE   (c1, c2) IN (  SELECT c1, c2  FROM   test  GROUP  BY 1,2  HAVING count(*) > 1 ) ORDER   BY 1; 

Questions similaires