mysql 預存程序 重複資料刪除

標籤：ar   sql   mysql   ef   rom   c   儲存   select   

DELIMITER $$

CREATE PROCEDURE `delRepeatCA`()

BEGIN

DECLARE tally INT DEFAULT 0;

SELECT COUNT(rs.c_CA) INTO tally FROM          --------賦值
(SELECT COUNT(c_CA) AS c_CA FROM t_CA
GROUP BY c_CA HAVING COUNT(1) > 1) rs

WHILE tally>0 DO　　　　　　--------迴圈

IF(tally>0)THEN
　　　　　　　　　　　　　　　　------------重複資料刪除
DELETE t_CAAS a FROM t_CAAS a, (
SELECT MIN(c_id)AS c_id FROM t_CA GROUP BY c_CA HAVING COUNT(1) > 1)AS b
WHERE a.c_id = b.c_id;

SELECT COUNT(rs.c_CA) INTO tally FROM
(SELECT COUNT(c_CA) AS c_CA FROM t_CA
GROUP BY c_CA HAVING COUNT(1) > 1) rs


END IF;

END WHILE;

END$$

DELIMITER ;