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

分享到：

2014-08-11 17:11

浏览 750

分类:数据库

评论