数据库去重sql

原创于 2021-08-24 08:52:25 发布 · 133 阅读

·

1

·

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

查询重复项

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)