我有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 BYHAVING子句。

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)

10-05 21:28
查看更多