我的数据库中有一个包含两列的表:name
和families
,它们都是varchar
。例如:
name families
----------------------
A 'B','C'
B 'A'
C 'A','B'
...
现在我想要得到行,它们的名称在第一行的族列中提到。我试过:
SELECT * FROM t JOIN (SELECT families FROM t WHERE name='A') AS z WHERE name IN (z.families)
但它不返回任何行。问题出在哪里?
您应该有一个表,每个名称和族有一行:
name family
A B
A C
B A
. . .
那么你的查询很简单:
select nf.*
from name_families nf join
name_families nf2
on nf2.family= nf.family and
nf2.name = 'A' and
nf2.name <> fn.name;
在类似tis:的自联接中使用函数FIND_IN_SET()
SELECT DISTINCT t1.name
FROM t t1 INNER JOIN t t2
ON FIND_IN_SET(t1.name, REPLACE(t2.families, '''', ''))
WHERE t2.name = 'A'
或者:
SELECT DISTINCT t1.name
FROM t t1 INNER JOIN t t2
ON FIND_IN_SET(QUOTE(t1.name), t2.families)
WHERE t2.name = 'A'
或者没有加入:
SELECT name
FROM t
WHERE FIND_IN_SET(QUOTE(name), (SELECT families FROM t WHERE name = 'A'))
请参阅演示
结果:
name B C