mysql剔除冗余数据

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

-- -- 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;