连接和多重条件 [英] Join and multiple and conditions

问题描述

我有用户表

ID     NAME
1      John
2      Mike
3      Jack

以及具有属性和用户ID的表

and table with attributes and user IDs

USER   ATTRIBUTE
1      1
1      2
2      4

我需要选择所有具有属性1和2的用户(因此，在此示例中，用户#1为John).属性可以超过两个.

I need to select all users with attribute 1 AND 2 (so, in this example user #1 John). Attributes can be more than two.

我尝试过

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，也不需要加入.像这样:

You will need to use a combination of IN() and GROUP BY ... HAVING to achieve this. Also no need for a join if all you need is user ID's. So something like:

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和名称，您可以简单地将此记录集从上面的查询派生出来，作为对用户表的过滤器:

If you need user id's and names you can simply join this record set derived from the query above as a filter to the users table:

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

这篇关于连接和多重条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！