时间戳

首页 > 解决方案 > sqlite 删除 a 列和 b 列不在前 n 项中的所有结果

sql - sqlite 删除 a 列和 b 列不在前 n 项中的所有结果

问题描述

可以说我有下表

a    b    c
-----------
1    1    5
1    2    3
4    1    2
1    2    4
4    2    10

我想删除前 n 行中没有一个与该行在 a 和 b 中具有相同值的所有行。

因此，例如，各种 n 的结果表将是

n = 1

a    b    c
-----------
1    1    5

// No row other than the first has a 1 in a, and a 1 in b

n = 2

a    b    c
-----------
1    1    5
1    2    3
1    2    4

// The fourth row has the same values in a and b as the second, so it is not deleted. The first 2 rows of course match themselves so are not deleted

n = 3

a    b    c
-----------
1    1    5
1    2    3
4    1    2
1    2    4

// The fourth row has the same values in a and b as the second, so it is not deleted. The first 3 rows of course match themselves so are not deleted

n = 4

a    b    c
-----------
1    1    5
1    2    3
4    1    2
1    2    4 

// The first 4 rows of course match themselves so are not deleted. The fifth row does not have the same value in both a and b as any of the first 4 rows, so is deleted.

我一直在尝试使用 not in 或 not exists 来解决此问题，但由于我对不只匹配 1 或整个记录的两列感兴趣，所以我很挣扎。

标签： sqlsqlite

解决方案

由于您没有定义特定的顺序，因此结果并未完全定义，而是取决于实现的任意选择，即在 limit 子句中首先计算哪些行。例如，不同的 SQLite 版本可能会给您不同的结果。话虽如此，我相信您需要以下查询：

select t1.* from table1 t1, 
(select distinct t2.a, t2.b from table1 t2 limit N) tabledist 
where t1.a=tabledist.a and t1.b=tabledist.b;

您应该将 N 替换为所需的行数

编辑：因此，要直接从现有表中删除，您需要以下内容：

with toremove(a, b, c) as 
    (select * from table1 tt 
    EXCEPT select t1.* from table1 t1, 
    (select distinct t2.a, t2.b from table1 t2 limit N) tabledist 
    where t1.a=tabledist.a and t1.b=tabledist.b) 
delete from table1 where exists 
(select * from toremove 
where table1.a=toremove.a and table1.b=toremove.b and table1.c=toremove.c);

推荐阅读