sql - SQL:删除GroupBy中的所有NOT MAX记录

Question

我的目标是从表中删除不是分组CaseKey的MAX（recordDate）的所有记录。因此，如果我有3组3个casekey的9条记录，并且每个casekey都有3个日期。我将删除每个集合的2个较低日期，并得出3个记录，仅剩下每个记录的MAX（recordDate）。

我有以下SQL查询：

    DELETE FROM table
    WHERE tableID NOT IN (
    SELECT tableID
    FROM (
    Select MAX(recordDate) As myDate, tableID From table
    Group By CaseKey
    ) As foo
    )

我收到错误：
第3行出错...在选择列表中，列'table.tableID'无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

显然，我可以将tableID添加到Group By子句中，但是该语句的结果不正确，并且返回所有行，而不仅仅是返回分组的CaseKeys的MAX recordDate。

服务器现在关闭了，但是明显的答案是：（来自WildPlasser答案的微小调整）

DELETE zt FROM ztable zt
WHERE EXISTS (
    SELECT * FROM ztable ex
    WHERE ex.CaseKey = zt.CaseKey
    AND ex.recordDate > zt.recordDate
);

换句话说，对于zt中的每个记录，运行查询以查看同一记录是否也具有具有更高recordDate的记录。如果是这样，则WHERE EXISTS语句通过并删除该记录，否则WHERE语句失败并且该记录是其自己的MAX recordDate。

谢谢WildPlasser，我以某种方式炸毁了这种简单的方法。

Answer 1

MAX的一个特殊属性：没有记录的值大于max。因此，我们可以删除所有具有相同CaseKey但具有更高recordDate记录的记录：

DELETE FROM ztable zt
WHERE EXISTS (
    SELECT *
    FROM ztable ex
    WHERE ex.CaseKey = zt.CaseKey
    AND ex.recordDate > zt.recordDate
    );

顺便说一句：以上查询（以及 MAX()版本）假定只有一个记录的最大日期。可能有联系。

如果是联系，则需要在where子句中添加一个额外的字段；作为决胜局。假设 TableId可以这样工作，查询将变为：

DELETE FROM ztable zt
WHERE EXISTS (
    SELECT *
    FROM ztable ex
    WHERE ex.CaseKey = zt.CaseKey
    AND (   ex.recordDate > zt.recordDate
        OR (ex.recordDate = zt.recordDate AND ex.TableId > zt.TableId)
        )
    );