加入和多和条件

Question

我有用户表

ID  NAME 
1  John 
2  Mike 
3  Jack 

和表属性和用户ID

USER ATTRIBUTE 
1  1 
1  2 
2  4 

，我需要选择与属性1和2（因此所有用户，在这个例子中用户＃1约翰）。属性可以超过两个。

心中已经试过

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

但当然不工作..

Answer 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 

Answer 2

having子句可以和使用

SELECT u.id FROM user u 
INNER JOIN attributes a ON u.id = a.user 
group by u.id 
having (sum(case when attribute in (1,2) then 1 else 0 end)) =2 

Answer 3

您正在寻找的人存在的所有用户属性1 和 2.解决此问题的一种方法 - 顾名思义 - 是EXISTS子句：

select * 
from users u 
where exists 
(
    select * 
    from user_attributes ua 
    where ua.user = u.id 
    and ua.attribute = 1 
) 
and exists 
(
    select * 
    from user_attributes ua 
    where ua.user = u.id 
    and ua.attribute = 2 
); 

另一种方法是：找到所有具有两个属性的用户ID，然后从用户表中选择。

select * 
from users 
where id in 
(
    select user 
    from user_attributes 
    where attribute in (1,2) 
    group by user 
    having count(*) = 2 
); 

的情况下有在属性重复条目，你将有count(distinct attribute)更换count(*)。

还有其他方法可以解决这个问题。我认为这两个提到的是相当直接的。

Answer 4

你需要一个group by ....有

SELECT u.name 
FROM 
    users u 
JOIN 
    attributes a 
    ON u.id = a.user 
WHERE a.id IN (1,2) 
GROUP BY u.name 
    HAVING COUNT(*) = 2