数据库去重sql

飞上天的橘子

于 2021-08-24 08:52:25 发布

阅读量133

点赞数 1

CC 4.0 BY-SA版权

文章标签： sql

本文链接：https://blog.youkuaiyun.com/qq_43291207/article/details/119882109

查询重复项

select * from "JJYX"."USERVALUE4" a where
 (a.USERMETERID,a.COLLECTTIME) 
 in(select USERMETERID,COLLECTTIME from "JJYX"."USERVALUE4" group by USERMETERID,COLLECTTIME having count(*) > 1)

删除重复项

delete from "JJYX"."USERVALUE4" a 
where (a.USERMETERID,a.COLLECTTIME) 
in 
(select USERMETERID,COLLECTTIME from "JJYX"."USERVALUE4" group by USERMETERID,COLLECTTIME having count(*) > 1) 
and rowid not in (select min(rowid) from "JJYX"."USERVALUE4" group by USERMETERID,COLLECTTIME having count(*)>1)