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;