您可以使用EXISTS
半连接来识别候选人:
选择想要的行:
SELECT * FROM tbl t
WHERE NOT EXISTS (
SELECT *
FROM tbl
WHERE col1 = t.col1
AND id = t.id - 1
)
ORDER BY id;
删除不需要的行:
DELETE FROM tbl AS t
-- SELECT * FROM tbl t -- check first?
WHERE EXISTS (
SELECT *
FROM tbl
WHERE col1 = t.col1
AND id = t.id - 1
);
这有效地删除了每一行,其中前一行具有相同的值col1
,从而达到您的设定目标:只有每次突发的第一行幸存下来。
我留下了评论SELECT
声明,因为你应该always在执行此操作之前检查要删除的内容。
非连续ID的解决方案:
如果您的 RDBMS 支持CTEs https://en.wikipedia.org/wiki/Common_table_expression and 窗函数 https://en.wikipedia.org/wiki/Window_function_%28SQL%29#Window_function(例如 PostgreSQL、Oracle、SQL Server...但是not SQLite v3.25之前 https://sqlite.org/releaselog/3_25_0.html、MS Access 或 MySQLv8.0.1之前 https://dev.mysql.com/doc/refman/8.0/en/with.html),有一个优雅的方法:
WITH cte AS (
SELECT *, row_number() OVER (ORDER BY id) AS rn
FROM tbl
)
SELECT id, col1
FROM cte c
WHERE NOT EXISTS (
SELECT *
FROM cte
WHERE col1 = c.col1
AND rn = c.rn - 1
)
ORDER BY id;
另一种方式完成这项工作没有那些细节(应该适合你):
SELECT id, col1
FROM tbl t
WHERE (
SELECT col1 = t.col1
FROM tbl
WHERE id < t.id
ORDER BY id DESC
LIMIT 1) IS NOT TRUE
ORDER BY id;