删除不同日期下冗余数据失败，求正确SQL实现方案

我尝试删除表中的冗余数据，但针对不同日期的场景未生效。使用的查询语句如下：

WITH date AS
(SELECT ID, 
MIN(time) AS minDate, 
MAX(time) AS maxDate
FROM tablename
GROUP BY ID)
DELETE FROM tablename
  WHERE 
    NOT EXISTS 
      (SELECT 1 FROM tablename
        WHERE tablename.ID = d.ID 
          AND tablename.time IN (d.minDate, d.maxDate));

当前数据表 #

期望得到的数据表 #

内容的提问来源于stack exchange，提问作者mycode binary