firebird中删除多列重复的数据

原本不复杂，一条sql语句就可以完成。思路是先根据列分组，找出分组后数据都于一条的，然后从中删除除了行id最小的其他数据。Oracle中即时字段中没有能表示行id的，每行都自带一个rowid，sql语句：

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

但是firebird中没有自带的行id，那就用个临时表，增加一个自增的id字段，然后把其他所有数据拷贝到临时表中。但是firebird临时表用法也是比较奇葩，也没有像mysql中那样的 auto increament的功能，只能用个generator在弄个trigger，来模拟auto increasement。资料又少，用firebird有点坑，sql脚本如下：

-- 存储过程用于删除刷卡信息表中的重复数据，然后把以前索引改为唯一性索引
-- 创建临时表，增加行id，用于删除重复数据
create global temporary table temp_door_swipe_table (
id integer not null,
device_id varchar(50),
card_id varchar(20),
user_id varchar(50),
io_fg smallint,
swipe_time timestamp,
swipe_outtime timestamp,
remark varchar(200),
door_log integer
)on commit preserve rows;
-- 设置生成器与触发器，用于数据插入时候id自增
create generator gen_temp_door_swipe;
set generator gen_temp_door_swipe to 0;
SET TERM ^ ;
create or alter trigger temp_door_swipe_table for temp_door_swipe_table
active before insert position 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_temp_door_swipe,1);
end
^
SET TERM ; ^
-- 数据放入临时表
insert into temp_door_swipe_table(device_id, card_id, user_id, io_fg, swipe_time, swipe_outtime, remark, door_log) select distinct * from t_door_swipeinfo;
-- 清空表
delete from t_door_swipeinfo;
commit;
-- 修改索引为唯一性索引
drop index idx_swipe_group;
create unique index idx_swipe_group on t_door_swipeinfo (device_id, card_id, swipe_time);
-- 首先把device_id, card_id, swipe_time 不重复的数据放入表
insert into t_door_swipeinfo
select a.device_id, a.card_id, a.user_id, a.io_fg, a.swipe_time, a.swipe_outtime, a.remark, a.door_log
from
(temp_door_swipe_table a right join
(select device_id, card_id, swipe_time from temp_door_swipe_table group by device_id, card_id, swipe_time having count(*) = 1)
b on a.device_id = b.device_id and a.card_id = b.card_id and a.swipe_time = b.swipe_time);
-- 首先把device_id, card_id, swipe_time 重复的第一条数据放入表
insert into t_door_swipeinfo
select a.device_id, a.card_id, a.user_id, a.io_fg, a.swipe_time, a.swipe_outtime, a.remark, a.door_log
from temp_door_swipe_table a where a.id in
(select min(id) from temp_door_swipe_table group by device_id, card_id, swipe_time having count(*)>1);
commit;
-- 清空临时表
drop trigger temp_door_swipe_table;
drop generator gen_temp_door_swipe;
drop table temp_door_swipe_table;
commit;

global temporary最后有两个参数，一个意思是一个事务结束后把表清空，一个意思是一次连接结束后把表清空。

“ON COMMIT DELETE ROWS creates a transaction-level GTT (the default), ON COMMIT PRESERVE ROWS a connection-level GTT.”

这个表不会自动删，最后sql中直接给删掉了，这里用不用临时表意义不大了。
在firebird中也没法用例如where Column1, Column2 in 这样的语法，只能换个思路，创建临时表后，用表连接方式先从临时表里面把分组后唯一的拷贝到原数据表，在把临时表中分组后有多条的，id最大或最小的拷贝到原数据表。

http://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html