如果数据库中有重复数据,则对重复的数据保留一条,其他删出,以交易日期表举例,关联字段为唯一索引字段。
one:
delete exchangedate a
where exists(select 1
from (select *
from (select init_date,
finance_type,
exchange_type,
min(rowid) as row_id,
count(*) as row_count
from exchangedate
group by init_date, finance_type, exchange_type)
where row_count > 1) b
where a.finance_type = b.finance_type
and a.exchange_type = b.exchange_type
and a.init_date = b.init_date
and a.rowid <> b.row_id);
two:
delete exchangedate a
where a.rowid > (select min(rowid)
from exchangedate b
where a.finance_type = b.finance_type
and a.exchange_type = b.exchange_type
and a.init_date = b.init_date);
扩展:按某个字段分组,然后将这个字段下数据只保留一条记录。
delete hs_user.functiontomenu a
where exists(select 1
from (select menu_id,min(rowid) as row_id
from hs_user.functiontomenu
group by menu_id) b
where a.menu_id = b.menu_id
and a.rowid > b.row_id);