delimiter;;CREATEDEFINER=`root`@`localhost`PROCEDURE`deldata`(IN`delnum`int,IN`filter`int)BEGINDECLARE dev VARCHAR(50);##定义执行满足删除条件的设备IDDECLARE cnt INTDEFAULT0;##定义满足删除条件的设备数量,直到为0时退出SELECTcount(tmp.device_id)into cnt
FROM(SELECT device_id
FROM(SELECT device_id,COUNT(1) num
FROM soc_eventdata
WHERE event_time >'2022-10-10'GROUPBY device_id
) t
WHERE t.num >2) tmp;WHILE cnt >0doSELECT device_id into dev
FROM(SELECT device_id,COUNT(1) num
FROM soc_eventdata
WHERE event_time >'2022-10-10'GROUPBY device_id
) t
WHERE t.num > filter limit1;DELETEFROM soc_eventdata
WHERE device_id = dev and event_time >'2022-10-10'LIMIT delnum;ENDWHILE;END;;delimiter;