我已经研究这个问题几个小时了。我有两个表,animals
和characteristics
,它们具有多对多关系,并形成第三个表,animal_char
。在表animal_char
中,a ID是表animals
中ID的外键,CID是表characteristics
中ID的外键。characteristics
还有一个description属性,这就是我要搜索的内容。我的目标是能够在characteristics
中搜索一个、两个或三个描述,并让输出显示包含这些描述的每个动物ID的行。此外,我希望能够看到每个动物ID的所有其他特征描述,即使我没有搜索它们。
我试过无数次查询,但下面是一个不起作用的示例:
SELECT a.ID,
a.name,
GROUP_CONCAT(c.description ORDER BY c.description SEPARATOR ', ')
FROM animals a
INNER JOIN animal_char ac ON ac.AID = a.ID
INNER JOIN characteristics c ON c.ID = ac.CID
WHERE c.description = 'Lab'
GROUP BY ID;
此代码的问题:
我只能搜索一个特征描述
结果只显示Lab是一个特征;它们不显示每行的任何其他特征。我希望能够看到一些行具有多个特性。
提前谢谢你!
编辑
好吧,我找到了一次搜索一个特征的查询。我可以说这对这个项目来说已经足够好了。问题是:
SELECT a.ID, a.name,
GROUP_CONCAT(c.description ORDER BY c.description SEPARATOR ', ')
FROM animals a
INNER JOIN animal_char ac ON a.ID = ac.AID
INNER JOIN characteristics c ON c.ID = ac.CID
WHERE a.ID IN
(SELECT ac.AID FROM characteristics c
INNER JOIN animal_char ac ON ac.CID = c.ID
WHERE c.description = 'Lab')
GROUP BY a.ID
最佳答案
我看到一些有用的连接方式(具有组合特征的动物):
SELECT a.ID, a.name,
GROUP_CONCAT(c.description ORDER BY c.description SEPARATOR ', ')
FROM animals a
INNER JOIN animal_char ac ON a.ID = ac.AID
INNER JOIN characteristics c ON c.ID = ac.CID
WHERE a.ID IN
(SELECT ac.AID FROM characteristics c
INNER JOIN animal_char ac ON ac.CID = c.ID
WHERE c.description = 'Lab'
OR c.description = 'second'
OR c.description = 'third'
GROUP BY ac.AID
HAVING COUNT(c.description) = 3)
GROUP BY a.ID
在子查询中,它将按所需的描述进行筛选,然后按动物id进行分组,并再次仅筛选具有所需描述数量的描述。(尽管描述必须不同)这也允许一些“2/3特征”搜索。如果你需要的话。。。
第二种方法是多重的,其中:
SELECT a.ID, a.name,
GROUP_CONCAT(c.description ORDER BY c.description SEPARATOR ', ')
FROM animals a
INNER JOIN animal_char ac ON a.ID = ac.AID
INNER JOIN characteristics c ON c.ID = ac.CID
WHERE a.ID IN
(SELECT ac.AID FROM characteristics c
INNER JOIN animal_char ac ON ac.CID = c.ID
WHERE c.description = 'Lab')
AND a.ID IN
(SELECT ac.AID FROM characteristics c
INNER JOIN animal_char ac ON ac.CID = c.ID
WHERE c.description = 'second')
AND a.ID IN
(SELECT ac.AID FROM characteristics c
INNER JOIN animal_char ac ON ac.CID = c.ID
WHERE c.description = 'third')
GROUP BY a.ID
或者正如Mindor先生所建议的那样(我觉得这一点比较平易近人,当然也可以放在where子句中):
SELECT a.ID,
a.name,
GROUP_CONCAT(c.description ORDER BY c.description SEPARATOR ', ')
FROM animals a
INNER JOIN animal_char ac ON ac.AID = a.ID
INNER JOIN characteristics c ON c.ID = ac.CID
INNER JOIN characteristics fc1 ON ac.CID = fc1.ID AND fc1.description = 'Lab'
INNER JOIN characteristics fc2 ON ac.CID = fc2.ID AND fc2.description = 'second'
INNER JOIN characteristics fc3 On ac.CID = fc3.ID AND fc3.description = 'third'
GROUP BY a.ID;
我不确定这三个中哪一个是最有表现的。所以你可以试着。。。或者不。^^
关于mysql - 多对多关系中的SELECT查询未显示所有“标签”,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/38730228/