连接和多重条件

一尘不染

连接和多重条件

sql

我有用户表

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

但是当然不行..

阅读 126

2021-05-16

共1个答案

一尘不染

您将需要使用的组合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

2021-05-16