我有3张桌子,恢复了它们:
:::NEWS:::
ID | TITLE
1 | New A
2 | New B
:::TAGTONEW:::
NEWID | TAGID
1 | 1
1 | 2
2 | 2
2 | 3
:::TAGS:::
ID | TAG
1 | religion
2 | sport
3 | politic
这样做的目的是搜索与给定标签列表匹配的新闻(例如带有运动和宗教标签的新闻)。好。问题是当我做类似的事情时:
SELECT * FROM news JOIN tagtonew ON news.id = newid JOIN tags ON tagid = tags.id
WHERE tag IN ('religion','sport');
结果将是3行,其中2行重复
New A
。好的,我可以使用GROUP BY news.id
仅获得一行,但是问题是我需要返回与新A匹配的所有标签(我需要返回新A与体育和宗教有关),我该怎么办?去做?是否要重传newid?提前致谢。编辑
以上结果将是:
New A - religion
New A - sport
New B - sport
如果我分组会像:
New A - religion
New B - sport
但是我需要知道A也与运动有关,因为我需要以JSON返回它。所以我需要输出是这样的:
New A - (religion, sport)
New B - sport
最佳答案
您只需要使用GROUP BY
和HAVING
子句。
SELECT a.Title
FROM news a
INNER JOIN tagtonew b
ON a.id = b.newid
INNER JOIN tags c
ON b.tagid = c.id
WHERE c.tag IN ('religion','sport')
GROUP BY a.Title
HAVING COUNT(*) = 2
SEE Previous SO Answer ((rows that matches to multiple conditions)
更新1
使用
GROUP_CONCAT
SELECT a.MovieName, '(' || GROUP_CONCAT(b.CategoryName) || ')' AS List
FROM MovieList a
INNER JOIN CategoryList b
ON a.ID = b.MovieID
WHERE b.CategoryName IN ('Comedy','Romance')
GROUP BY a.MovieName
SQLFiddle Demo (records may be different but may have same thought)