sqlite 如何添加另一个子查询？

Question

我在 sqlite 查询中有:

   DELETE FROM test      WHERE a NOT IN (            SELECT * FROM (                   SELECT MAX(a) FROM test GROUP BY z )                     )

它工作正常。如果有另一行具有相同的“a”最大值，问题就开始了。在这种情况下，不会删除任何行。如何添加另一个条件(子查询)- 当两个最大值相同时，b 列中的数字决定应删除哪一行？

Answer 1

如果您想为每个 z 保留具有最大 a 的行并删除其他行，那么您可以使用 EXISTS:

DELETE FROM test
WHERE EXISTS (
  SELECT 1 FROM test t
  WHERE t.z = test.z AND a > test.a
)

或者使用相关的子查询:

DELETE FROM test
WHERE a < (SELECT MAX(t.a) FROM test t WHERE t.z = test.z)

或使用 ROW VALUES使用运算符 IN:

DELETE FROM test
WHERE (z, a) NOT IN (SELECT z, MAX(a) FROM test GROUP BY z)

编辑:

如果您有另一列 b 解决了关系，以防超过 1 行具有相同的最大值 a 然后也使用 ROW_NUMBER() 或 RANK() 窗口函数:

DELETE FROM test
WHERE (z, a, b) IN (
  SELECT z, a, b
  FROM (
    SELECT z, a, b, ROW_NUMBER() OVER (PARTITION BY z ORDER BY a DESC, b DESC) rn
    FROM test
  ) t
  WHERE rn > 1  
)

或者删除所有 rowid 与 rowid 最大 a(和最大 b如果有联系)对于每个 z:

DELETE FROM test
WHERE rowid <> (SELECT t.rowid FROM test t WHERE t.z = test.z ORDER BY t.a DESC, t.b DESC LIMIT 1)

或者使用EXISTS:

DELETE FROM test
WHERE EXISTS (
  SELECT 1 FROM test t
  WHERE (t.z = test.z) AND (t.a > test.a OR (t.a = test.a AND t.b > test.b))
);