Home  >  Article  >  Database  >  mysql剔除冗余数据

mysql剔除冗余数据

WBOY
WBOYOriginal
2016-06-07 16:27:091923browse

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;

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn