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

来源：百度知道 编辑：UC知道 时间：2024/06/23 13:34:13
小弟初学者，sql内有个表如gl_projtr 有列id（主键），tr_date,tr_num,tr_id,tr_note 除了主键ID 其他全部重复的两条或者三条记录。如何删除只保留一条记录呢，保留哪个ID都可以。
例如：id tr_date tr_num tr_id tr_note
1 2009-8-1 0002 015 张三
3 2009-8-31 0035 013 张五
2 2009-8-1 0002 015 张三
7 2009-6-31 0135 012 张六

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)

如果是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
)

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