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;

本文原創發布php中文網，轉載請注明出處，感謝您的尊重！

總結

以上是生活随笔為你收集整理的mysql消除冗余_mysql剔除冗余数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。