当第 2 列匹配时，查找第 1 列"彼此不喜欢"的行

Question

我在数据库fls2中有一个包含2列的表，它是Sqlite；

name        |    sha256
------------|------------------
ab/ac/ad    |    12345
ab/ad/af    |    12345
zx/ad/af    |    12345

对于特定的sha256，我想找到"name like"ab%和"name not like"ab%都为true的名称。因此，在上述情况下，这3行共享一个sha256"12345"，我想将其视为子数据集。在该数据集中，如果"name like"ab%"one_answers"name not like"ab%"都为true(显然是针对2个或多个不同的行(，我希望返回所有行。

我正在做的是搜索两个不同的顶级目录中存在相同文件(由其sha256标识(的情况。

我知道在获取数据后如何在perl中完成这项工作，但理想情况下，如果我能在DB中完成这一工作，那会更好。我试过

select name 
from 
fls2 
where 
sha256 = (select sha256 from fls2 where name like 'ab%') 
and 
name not like 'ab%';

但它没有返回任何行(我知道至少有一些行，因为我手动找到了它们(。

Answer 1

使用聚合和having:

select sha226, group_concat(name) as names
from t
group by sha226
having sum(case when name like 'ab%' then 1 else 0 end) > 0 and
sum(case when name not like 'ab%' then 1 else 0 end) > 0;

这会将列表中的所有名称放在同一行。

Answer 2

使用EXISTS:

select * from fls2 f
where
exists (select 1 from fls2 where sha256 = f.sha256 and name like 'ab%')
and
exists (select 1 from fls2 where sha256 = f.sha256 and name not like 'ab%')

请参阅演示
或具有sum()窗口功能：

select f.name, f.sha256
from (
select *, 
sum(name like 'ab%') over (partition by sha256) sum1,
sum(name not like 'ab%') over (partition by sha256) sum2
from fls2
) f
where f.sum1 > 0 and f.sum2 > 0

请参阅演示
结果：

| name     | sha256 |
| -------- | ------ |
| ab/ac/ad | 12345  |
| ab/ad/af | 12345  |
| zx/ad/af | 12345  |

Answer 3

在该数据集中，如果"name like‘ab%’"one_answers"name not like‘ab%’"都为true(显然对于两个或多个不同的行(，我希望返回所有行。

您可以使用窗口函数：

select name, sha256
from (
select 
f.*,
max(case when name like 'ab%' then 1 end) over(partition by sha256) max_ab,
max(case when name not like 'ab%' then 1 end) over(partition by sha256) max_not_ab
from fls2 f
) t
where max_ab = 1 and max_not_ab = 1

在子查询中，窗口max()检查是否存在具有相同sha256且名称以'ab%'开头(相应地，不以(的记录。然后，外部查询对满足这两个条件的记录进行筛选。