SQL怎么删除一个表相同的其他的记录

发布网友 发布时间：2022-04-07 22:32

我来回答

共6个回答

热心网友 时间：2022-04-08 00:01

在select后加上distinct
比如说select distinct * from sys_codemap
你看看，肯定没有重复的记录了

热心网友 时间：2022-04-08 01:19

三步走(已测)：

SELECT * INTO #TMP FROM t_sys_codemap GROUP BY type,code,value,remark,id HAVING COUNT(*)>1

DELETE from t_sys_codemap WHERE EXISTS (SELECT * FROM #TMP WHERE #TMP.type=t_sys_codemap.type AND #TMP.code=t_sys_codemap.code AND #TMP.value=t_sys_codemap.value AND #TMP.remark=t_sys_codemap.remark AND #TMP.id=t_sys_codemap.id)

INSERT INTO t_sys_codemap SELECT * FROM #TMP

热心网友 时间：2022-04-08 02:54

不用建临时表什么什么的那么麻烦了,一句就搞定.
delete from t_sys_codemap where rowid != (select max(rowid) from t_sys_codemap group by type,value)

热心网友 时间：2022-04-08 04:45

oracle可以使用rowid
delete from t_sys_codemap where rowid not in (select max(rowid) from t_sys_codemap group by type,value)

热心网友 时间：2022-04-08 06:53

通过一个临时表过渡一下
insert into table1 select distinct field from t_sys_codemap
drop table t_sys_codemap
insert into t_sys_codemap select * from table1

热心网友 时间：2022-04-08 09:18

select distinct * from t_sys_codemap