SELECT *
From Tb_music_mp3
WHERE (Songtype in
(SELECT Songtype
From Tb_music_mp3
GROUP by Songtype
Having (COUNT (songtype) > 1)))
SELECT *
From tb_usertable_other_100000
WHERE (userid in
(SELECT userid
From tb_usertable_other_100000
GROUP by UserID
Having (COUNT (userid) > 1)))
SELECT *
From Tb_usertable_main
WHERE (userid in
(SELECT userid
From Tb_usertable_main
GROUP by UserID
Having (COUNT (userid) > 1)))
ORDER BY UserID
Delete
From tb_usertable_other_5600000
WHERE (id not in
(SELECT MIN (ID)
From tb_usertable_other_5600000
GROUP by UserID))
In large database applications, repeated records are often encountered for various reasons, resulting in redundancy of data and inconvenience of maintenance.
ID Custom
1 Chemical Company
2 Chemical Company
3 Chemical Company
4 Software Companies
5 Pharmaceutical Companies
6 Pharmaceutical Companies
Now would like to leave a duplicate, repeat the deletion, the result is as follows:
ID Custom
1 Chemical Company
4 Software Companies
5 Pharmaceutical Companies
Delete table where ID not in (select min (id) from table Group by custom)
SELECT *
From Tb_music_mp3
WHERE (id not in
(SELECT MIN (ID)
From Tb_music_mp3
GROUP by Songtype))
SELECT ID, Songtype, songname
From Tb_music_mp3 A
WHERE (Not EXISTS
(SELECT 1
From Tb_music_mp3 b
WHERE A.songtype = B.songtype and a.id > b.ID))
1. Using the rowID method
2. Using the group by method
3. Using the Distinct method
1. Using the rowID method
According to the ROWID attribute of the Oracle band, it is judged whether there are duplicates, as follows:
Check the data:
SELECT * FROM table1 a where rowid! = (select Max (ROWID)
From table1 b where a.name1=b.name1 and a.name2=b.name2 ...)
Delete data:
Delete from table1 a where rowid! = (select Max (ROWID)
From table1 b where a.name1=b.name1 and a.name2=b.name2 ...)
2.group by method
Check the data:
Select COUNT (num), max (name) from student--Lists the number of duplicate records and lists his Name property
GROUP BY Num
Having count (num) >1--grouped by Num to find duplicates of num columns in a table that occur more than once
Delete data:
Delete C
In this case, all the duplicates are deleted.
3. Using the distinct method-useful for small tables
CREATE TABLE Table_new as SELECT DISTINCT * FROM table1 Minux
TRUNCATE TABLE table1;
INSERT INTO table1 select * from Table_new;
Delete
From Tb_book_list
WHERE (id not in
(SELECT max (ID)
From Tb_book_list
GROUP by BookID))
(5065 rows affected)
SQL de-duplication