查找具有相同外键的行和集合成员满足条件

Question

有两个表user和user_set。这两个表都使用id列作为其主要键。

user具有列user_set_id作为外键，以指示其属于哪个用户设置。

user表：

 id   | user_set_id  | name     | active | no_in_set         
------+--------------+----------+--------+------------
    1 |            1 | Alice    | t      | 3
    2 |            3 | Bob      | f      | 4
    3 |            2 | Charlie  | t      | 3
    4 |            2 | Daniel   | f      | 1
...

user_set表：

id   | name  
-----+--------
   1 | set1
   2 | set2
   3 | set3
... 

一组总是有四个属于它的用户。我想找到满足以下条件的用户集：

• 用户1和用户2处于活动状态
• user3和user4不活动

（用户编号由user.no_in_set列标识。）

对于满足这些条件的每个集合，我想检索user1的名称。

Answer 1

hmmm。我正在考虑有条件的聚合：

select u.user_set_id
from user u
group by u.user_set_id
having bool_or( u.no_in_set = 1 and u.active) and
       bool_or( u.no_in_set = 2 and u.active) and
       bool_or( u.no_in_set = 3 and not u.active) and
       bool_or( u.no_in_set = 4 and not u.active) ;

Answer 2

您可以利用满足所有条件的设置将完全有4行的事实：

SELECT user_set_id, name
FROM user
WHERE no_in_set = 1 
    AND user_set_id IN (
        SELECT user_set_id 
        FROM user
        WHERE (no_in_set = 1 AND active) 
            OR (no_in_set = 2 AND active) 
            OR (no_in_set = 3 AND NOT active) 
            OR (no_in_set = 4 AND NOT active) 
        GROUP BY user_set_id 
        HAVING count(*) = 4)
ORDER BY user_set_id