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

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;