当前位置：网站首页>SQL: query duplicate data and delete duplicate data

SQL: query duplicate data and delete duplicate data

2022-04-23 20:24:00 【First code】

1. Single column 

select * from test
where name in (select name from test group by name having count
(name) > 1

  select * from [ Department information summary ]
where Effective or not = 1 and [ Department name ] in (select [ Department name ] from [ Department information summary ] where Effective or not =1 group by [ Department name ] having count
([ Department name ]) > 1) 
and Where in (select Where from[ Department information summary ] group by Where having count
( Where ) > 1)

2 Multiple columns 

SELECT a.* FROM test a,(
SELECT name,code
FROM test 
GROUP BY name,code
HAVING COUNT(1)>1

) AS b
WHERE a.name=b.name AND a.code=b.code

3 Delete duplicate data

Take deleting duplicate ID cards as an example 

delete from Test
where  ID number  in( select  ID number  from Test group by   ID number  having count( ID number ) > 1) and 
TK not in(select  max(TK) from Test group by   ID number  having count( ID number ) > 1 )

Query duplicate ID card 

select  ID number ,count(*) from Test group by  ID number  having count(*) > 1

版权声明
本文为[First code]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204210551267930.html

边栏推荐

猜你喜欢

随机推荐