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;下载本文
