mysql - 检索与标签匹配的项目，并同时检索与每个项目相关的所有标签

Question

我有与此问题相关的下表:

project
- projectId (PK)
- projectTitle
- projectDescription
- etc..

tag
- tagId
- tagName
- tagDescription
- etc...

project_tag
- projectId (PK / FK -> project.projectId)
- tagId (PK / FK -> tag.tagId)

我正在实现类似于 StackOverflow 的标签功能，因为人们可以查看由一个或多个标签标记的项目(在我的例子中是项目)列表。我想要做的是选择所有项目，这些项目至少标有我提供给查询的所有标签，但同时检索与每个单独项目相关的所有标签。

我现在所做的工作，但我感觉 WHERE IN 子句中的子查询效率非常低，因为这可能会针对每一行执行，对吗？

SELECT
    `project`.*,
    GROUP_CONCAT( DISTINCT `tagName` ORDER BY `tagName` SEPARATOR ' ' ) as `tags`
FROM
    `project`
JOIN
    `project_tag`
    USING ( `projectId` )
JOIN
    `tag`
    USING ( `tagId` )
WHERE
    `projectId` IN (
        SELECT
            `projectId`
        FROM
            `project_tag`
        JOIN
            `tag`
            USING ( `tagId` )
        WHERE
            `tagName` IN ( 'the', 'tags' )
        GROUP BY
            `projectId`
        HAVING
            COUNT( DISTINCT `tagName` ) = 2 # the amount of tags in the IN clause
    )
GROUP BY
    `projectId`

有没有办法加入 tag ，以便我能够同时检索 JOIN 项目的所有标签，而仅JOIN(至少)与我提供给查询的所有标签匹配的项目，而无需使用 WHERE IN 子句？

为了说明示例结果，请考虑以下示例项目:

projectId: 1, tags: php, cms, webdevelopment
projectId: 2, tags: php, cms, ajax
projectId: 3, tags: c#, cms, webdevelopment

搜索标签php和cms会得到(这些没有格式化为实际的mysql查询结果，只是为了说明相关数据):

projectId: 1, tags: php, cms, webdevelopment
projectId: 2, tags: php, cms, ajax

不仅仅是:

projectId: 1, tags: php, cms
projectId: 2, tags: php, cms

Answer 1

子查询是不相关的(可以取出来单独执行，不会出错)，所以应该执行一次。

使用子查询首先排除不匹配的项目，然后根据该项目连接回其他表可能会更有效。像这样的事情:-

SELECT project.*, GROUP_CONCAT( DISTINCT tagName ORDER BY tagName SEPARATOR ' ' ) as tags
FROM (SELECT projectId, COUNT( DISTINCT tagName ) AS TagCount
        FROM tag
        INNER JOIN project_tag USING (tagId)
        WHERE tagName IN ( 'the', 'tags' )
        GROUP BY projectId
        HAVING TagCount = 2) Sub1
INNER JOIN project ON Sub1.projectId = project.projectId
INNER JOIN project_tag USING (projectId)
INNER JOIN tag USING (tagId)
GROUP BY projectId

我假设您在 tagName 上有一个索引。