mysql删除冗余数据 -- -- 1. 查询冗余数据select t.id from t_lifeservice_orders t where t.orderstatus = 2 group by t.channelcode, t.ordernum, t.orderstatus having count(t.orderstatus) 1;-- -- 2. 定义删除冗余数据存储过程drop procedure if exists
mysql删除冗余数据
-- -- 1. 查询冗余数据select t.id from t_lifeservice_orders t where t.orderstatus = 2 group by t.channelcode, t.ordernum, t.orderstatus having count(t.orderstatus) > 1;-- -- 2. 定义删除冗余数据存储过程drop procedure if exists proc_delete_redundance; delimiter $ create procedure proc_delete_redundance() begin declare cid int; declare done boolean default false; declare cur cursor for select t.id from t_lifeservice_orders t where t.orderstatus = 2 group by t.channelcode, t.ordernum, t.orderstatus having count(t.orderstatus) > 1; declare continue handler for not found set done = true; -- declare continue handler for sqlstate '02000' set done = 1; open cur; fetch next from cur into cid; flag: while true do if done then leave flag; end if; delete from t_lifeservice_orders where id = cid; fetch next from cur into cid; end while; close cur; end$ delimiter ;-- ---- 3. 执行存储过程call proc_delete_redundance(); -- ---- 4. 删除存储过程drop procedure proc_delete_redundance;