如何在SQLite中添加另一个子查询？

Question

我在sqlite查询中有以下语句：

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

它可以正常工作。 问题在于如果有另一行具有相同的'a'最大值，则没有任何行被删除。 如何添加另一个条件（子查询） - 当存在两个相同的最大值时，列'b'中的编号确定应删除哪一行？

- dDo

---

我认为你不能在SQLite上使用WHERE IN进行此查询。这在其他支持元组语法的数据库（如MySQL）上可能是可行的。 - Tim Biegeleisen

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，用于解决存在多个具有相同最大值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与每个z的最大a（如果有并列，则为最大b）的rowid不同：

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))
);

- forpas

---

如果我有以下代码： 第一行：a=10，b=5，z=ppp； 第二行：a=20，b=4，z=ppp； 查询“第二行：a=20，b=4，z=ppp”后只剩下这一行。问题是当有以下代码时： 第一行：a=10，b=5，z=ppp； 第二行：a=10，b=4，z=ppp； 'a'相同，在查询后没有删除任何内容，但应该删除第二行。 - dDo

这个注释应该放在你的问题里面，以澄清需求。请看我的编辑后的答案。 - forpas

您可以在此处查看：https://www.db-fiddle.com/f/jvUzVqdJjpy9514JFS1w51/0 查询在语法上是正确的。也许您正在使用不支持窗口函数的旧版本SQLite，而这些函数非常高效。如果是这种情况，那么恐怕您将不得不使用性能较差的解决方案。 - forpas

检查我的上一个查询。 - forpas

在sqlite控制台中，它可以正常工作并且非常快。问题出在我的SqliteStudio上。 - dDo