SQL表内除了主键其他数据重复的如何删除只保留一条

2024-11-30 19:31:29

推荐回答（3个）

回答（1）：

delete from table where exists (select 1 from table where tr_date=table.tr_date and tr_num=table.tr_num and tr_id=table.tr_id and tr_note=table.tr_note group by tr_date,tr_num,tr_id,tr_note having count(*)>1)
and id not in (select max(id) from table group by tr_date,tr_num,tr_id,tr_note having count(*)>1)

回答（2）：

delete from gl_projtr where id not in(select min(id) from gl_projtr group by tr_note)

回答（3）：

如果是sql server 2005的话可以这样写
delete from gl_projtr
where id in
(
select id,row_number over(partition by tr_date,tr_num,tr_id,tr_note) as rn
from gl_projtr
where rn>1
)