时间戳

首页 > 解决方案 > Find all linked records using self join

mysql - Find all linked records using self join

问题描述

I have this table and I am trying to use self join. I am able to achieve only 50% of the expected results.

create table auto_correct(spell varchar(255), ne varchar(255));
insert into auto_correct values ('test', 'testing');
insert into auto_correct values ('adn', 'and');
insert into auto_correct values ('and', 'andru');
insert into auto_correct values ('testing', 'tested');
insert into auto_correct values ('this', 'that');
insert into auto_correct values ('junk', 'delete');
spell ne
test testing
adn and
and andru
testing tested
this that
junk delete

The word 'test' is linked to 'tested' through 'testing' The word 'adn' is linked to 'andru' through 'and' How do I find all such linked records and display along with non-linked rows?

The expected results are:

spell ne
test tested
adn andru
this that
junk delete

I have tried this:

select b.spell, a.ne 
from auto_correct as a 
left join auto_correct as b on a.spell = b.ne 
where b.spell is not null;

returns:

adn andru
test tested

The rest of the records where no match is found are missing from above output.

标签： mysqlsql

解决方案

WITH RECURSIVE
cte AS ( SELECT *
         FROM auto_correct
         WHERE NOT EXISTS ( SELECT NULL
                            FROM auto_correct ac
                            WHERE auto_correct.spell = ac.ne )
       UNION ALL
         SELECT cte.spell, auto_correct.ne
         FROM cte
         JOIN auto_correct ON cte.ne = auto_correct.spell )
SELECT *
FROM cte
WHERE NOT EXISTS ( SELECT NULL
                   FROM auto_correct
                   WHERE auto_correct.spell = cte.ne )

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e90235ce5dde96a7255f8afe1a19d334

推荐阅读