MySQL多对多查询问题

Question

这就是我的问题。我有一个多对多的表，叫做'user_has_personalities‘。在我的应用程序中，用户可以有多个个性，一个个性可以属于多个用户。

该表有两个整数列: user_id和personality_id。

我需要做的是获得至少具有我提供给查询的所有个性(一组可变大小的personality_ids )的所有用户。

例如，我希望获得具有‘d为4、5、7的个性的所有用户，但也可以具有其他一些个性。但是我需要这个查询来处理数量可变的想要的人物I，比如4，5，7，9，10。

有什么想法吗？

Answer 1

此查询执行以下工作：

select  user_id
from    user_has_personalities
where   personality_id in (<list-of-personality-ids>)
group by user_id
having count(*) = <numer-of-items-in-IN-list>

您需要为<list-of-personality-ids>提供一个逗号分隔的个人ids列表，还需要提供该列表中的项数。按照你的例子，你会得到：

select  user_id
from    user_has_personalities
where   personality_id in (4,5,7)
group by user_id
having count(*) = 3

这确保您只获得具有所有这些个性的用户。

Answer 2

SELECT  *
FROM    (
        SELECT  DISTINCT user_id
        FROM    user_has_personalities
        ) uhpo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    user_has_personalities uhpi
        WHERE   uhpi.user_id  = uhpo.user_id
                AND personality_id IN (4, 5, 6, 9, 10)
        LIMIT 1 OFFSET 4
        )

偏移值应为小于IN列表中的项数的1。

如果您在专用表格中列出了您的个性列表，请使用以下命令：

SELECT  *
FROM    (
        SELECT  DISTINCT user_id
        FROM    user_has_personalities
        ) uhpo
WHERE   (
        SELECT  COUNT(*)
        FROM    perslist p
        JOIN    user_has_personalities uhpi
        ON      uhpi.user_id = uhpo.user_id
                AND uhpi.personality_id = p.id
        ) =
        (
        SELECT  COUNT(*)
        FROM    perslist
        )

为了使其正常工作(且快速)，您需要在user_has_personalities (user_id, personality_id)上有一个UNIQUE索引(按此顺序)。

如果您有一个users表，并且几乎所有用户在user_has_personalities中都有一条记录，那么用它代替DISTINCT嵌套查询：

SELECT  user_id
FROM    users uhpo
WHERE   (
        SELECT  COUNT(*)
        FROM    perslist p
        JOIN    user_has_personalities uhpi
        ON      uhpi.user_id = uhpo.user_id
                AND uhpi.personality_id = p.id
        ) =
        (
        SELECT  COUNT(*)
        FROM    perslist
        )

Answer 3

SELECT a.user_id
FROM user_has_personalities a
JOIN user_has_personalities b ON a.user_id = b.user_id AND b.personality_id = 5
JOIN user_has_personalities c ON a.user_id = c.user_id AND b.personality_id = 7
WHERE a.personality_id = 4

以编程方式生成此列表将非常容易，但它并不像提供一个set那样简单。另一方面，它是高效的。