sql - Find rows with 'not like each other' column 1 when column 2 matches

Question

I have one table with 2 columns in database fls2 which is Sqlite;

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

I would like to find names where 'name like 'ab%' and 'name not like 'ab%' are both true for a particular sha256. So in the above case the 3 rows share a sha256 of '12345', and I would like to consider that a sub dataset. Within that dataset, if both 'name like 'ab%' and 'name not like 'ab%' are true (for 2 or more different rows obviously) I would like all the rows returned.

What I am doing is searching for cases where an identical file (identified by its sha256) is present in 2 different top level directories.

I know how to do this in perl after fetching the data but ideally if I could do this in the DB it would be far better. Ive tried

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

But its not returning any rows (and I know there are at least a few because I have found them manually).

标签： sqlsqlite

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;

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