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

Question

我的目标是从我的表中删除所有记录，这些记录不是分组CaseKey的MAX(recordDate)。如果我有9条记录，有3组3个casekey，每个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行出现错误…列的表。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
    );

BTW:上面的查询(以及MAX()版本)假设只有一条记录具有最大日期。可能有关系

在tie的情况下，您需要在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)
        )
    );

Answer 2

表达

从我的表中删除所有不是a的MAX(recordDate)的记录分组CaseKey

SQL中

为

DELETE FROM table t1
WHERE t1.recordDate <> 
  (SELECT MAX(recordDate)
   FROM table t2
   WHERE t2.CaseKey = t1.CaseKey)

Answer 3

您可以rank所有具有相同caseKey的记录，其中rank> 1仅返回较低的日期。这样你可以使用你的tableID。

DELETE FROM [table]
WHERE [tableID] IN
  (SELECT
    [sub].[tableID]
  FROM
  (
    SELECT 
      [tableID],
      Rank() OVER (PARTITION BY [caseKey] ORDER BY [recordDate] DESC, [tableID] DESC) AS [rank]
    FROM [table]
  ) AS [sub]
  WHERE [sub].[rank] > 1)