Trouver des valeurs doubles dans une table SQL

98

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

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) 

72

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

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 ) 

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;