sql - 只选择SQL中一列中重复值的第一行

Question

我有一个表，其中一列可能在突发中具有相同的值。像这样:

+----+---------+
| id |   Col1  | 
+----+---------+
| 1  | 6050000 |
+----+---------+
| 2  | 6050000 |
+----+---------+
| 3  | 6050000 |
+----+---------+
| 4  | 6060000 |
+----+---------+
| 5  | 6060000 |
+----+---------+
| 6  | 6060000 |
+----+---------+
| 7  | 6060000 |
+----+---------+
| 8  | 6060000 |
+----+---------+
| 9  | 6050000 |
+----+---------+
| 10 | 6000000 |
+----+---------+
| 11 | 6000000 |
+----+---------+

现在我想修剪 Col1 值重复的行，只选择第一次出现的行。
对于上表，结果应该是:

+----+---------+
| id |   Col1  | 
+----+---------+
| 1  | 6050000 |
+----+---------+
| 4  | 6060000 |
+----+---------+
| 9  | 6050000 |
+----+---------+
| 10 | 6000000 |
+----+---------+

我如何在 SQL 中执行此操作？
请注意，只有爆破行应该被删除，值可以在非爆破行中重复! id=1 & id=9 在中重复示例结果。

编辑:
我用这个实现了它:

select id,col1 from data as d1
where not exists (
    Select id from data as d2
    where d2.id=d1.id-1 and d1.col1=d2.col1 order by id limit 1)

但这只适用于 id 是连续的。由于 id(已删除的)之间存在间隙，查询中断。我该如何解决这个问题？

Answer 1

您可以使用 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 语句，因为您应该始终在执行操作之前检查将要删除的内容。

非连续ID的解决方案:

如果您的 RDBMS 支持 CTEs和 window functions (如 PostgreSQL、Oracle、SQL Server，...但不是 SQLite prior to v3.25、MS Access 或 MySQL prior to v8.0.1)，有一种优雅的方式:

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;