mysql - 连接和多重条件

我有用户表

ID     NAME
1      John
2      Mike
3      Jack

和带有属性和用户标识的表

USER   ATTRIBUTE
1      1
1      2
2      4

我需要选择所有具有属性1和2的用户（因此，在此示例中为用户＃1 John）。属性可以超过两个。

我试过了

SELECT * FROM user u LEFT JOIN attributes a ON u.id = a.user
WHERE a.attribute = 1 AND a.attribute = 2

但是当然不行

最佳答案

您将需要使用IN()和GROUP BY ... HAVING的组合来实现。如果您只需要用户ID，也不需要加入。所以像这样：

SELECT user, COUNT(attribute) AS attribute_count
FROM attributes
WHERE attribute IN(...) /* include your set of attributes here */
GROUP BY user
HAVING attribute_count = ? /* include number equal to number of attribute ID's in IN() above */

如果您需要用户ID和名称，您可以简单地将此记录集（从上面的查询派生而来）作为用户表的过滤器：

SELECT user.id, user.name
FROM user
INNER JOIN
  (
    SELECT user, COUNT(attribute) AS attribute_count
    FROM attributes
    WHERE attribute IN(...) /* include your set of attributes here */
    GROUP BY user
    HAVING attribute_count = ? /* include number equal to number of attribute ID's in IN() above */
  ) AS filter
  ON user.id = filter.user

关于mysql - 连接和多重条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26004866/