多关联行条件下的SQL单基表行查询需求及求解

给定示例数据 #

person表 #

id, email
1, 1@email.com
2, 2@email.com
3, 3@email.com
4, 4@email.com
5, 5@email.com

B表 #

id, maincode, subcode, is_obsolete
1, code1,a, null 
2, code1,b, null
3, code2,a, true
4, code2,b, true
5, code3,a, null
6, code4,b, null
7, code5,c, true

access_B表 #

id, person_id, b_id, removed_at
1,1,1,null
2,1,2,null
3,1,3,null
4,1,4,null
5,2,3,null
6,2,4,null
7,3,5,null
8,4,5,null
9,4,7,null
10,5,7,2021-06-10 10:00:00

三类查询实现 #

1. 查询removed_at为null的access_B数据，要求对应person同时拥有is_obsolete为true和null的B记录 #

预期结果：

person_email, id, person_id, b_id, removed_at
1@email.com,1, 1, 1,null
1@email.com,2, 1, 2,null
1@email.com,3, 1, 3,null
1@email.com,4, 1, 4,null

SQL语句：

SELECT p.email AS person_email, ab.*
FROM access_B ab
JOIN person p ON p.id = ab.person_id
WHERE ab.removed_at IS NULL
AND EXISTS (
    SELECT 1
    FROM access_B ab1
    JOIN B b1 ON ab1.b_id = b1.id
    WHERE ab1.person_id = ab.person_id
      AND b1.is_obsolete IS TRUE
      AND ab1.removed_at IS NULL
)
AND EXISTS (
    SELECT 1
    FROM access_B ab2
    JOIN B b2 ON ab2.b_id = b2.id
    WHERE ab2.person_id = ab.person_id
      AND b2.is_obsolete IS NULL
      AND ab2.removed_at IS NULL
);

逻辑说明：通过两个EXISTS子查询分别验证当前person同时存在关联的is_obsolete=true和is_obsolete=null且removed_at=null的B记录，仅返回满足条件的access_B数据。

2. 查询removed_at为null的access_B数据，要求对应person仅拥有is_obsolete为true的B记录 #

预期结果：

person_email, id, person_id, b_id, removed_at
2@email.com,5,2,3,null
2@email.com,6,2,4,null

SQL语句：

SELECT p.email AS person_email, ab.*
FROM access_B ab
JOIN person p ON p.id = ab.person_id
WHERE ab.removed_at IS NULL
AND EXISTS (
    SELECT 1
    FROM access_B ab1
    JOIN B b1 ON ab1.b_id = b1.id
    WHERE ab1.person_id = ab.person_id
      AND b1.is_obsolete IS TRUE
      AND ab1.removed_at IS NULL
)
AND NOT EXISTS (
    SELECT 1
    FROM access_B ab2
    JOIN B b2 ON ab2.b_id = b2.id
    WHERE ab2.person_id = ab.person_id
      AND b2.is_obsolete IS NULL
      AND ab2.removed_at IS NULL
);

逻辑说明：用EXISTS确认person存在关联的is_obsolete=true的B记录，同时用NOT EXISTS排除存在is_obsolete=null的B记录的person，仅保留仅拥有is_obsolete=true关联记录的person的access_B数据。

3. 查询removed_at为null的access_B数据，要求对应person拥有is_obsolete为true的B记录 #

预期结果：

person_email, id, person_id, b_id, removed_at
1@email.com,1,1,1,null
1@email.com,2,1,2,null
1@email.com,3,1,3,null
1@email.com,4,1,4,null
2@email.com,5,2,3,null
2@email.com,6,2,4,null

SQL语句：

SELECT p.email AS person_email, ab.*
FROM access_B ab
JOIN person p ON p.id = ab.person_id
WHERE ab.removed_at IS NULL
AND EXISTS (
    SELECT 1
    FROM access_B ab1
    JOIN B b1 ON ab1.b_id = b1.id
    WHERE ab1.person_id = ab.person_id
      AND b1.is_obsolete IS TRUE
      AND ab1.removed_at IS NULL
);

逻辑说明：通过EXISTS子查询验证当前person至少存在一条关联的is_obsolete=true且removed_at=null的B记录，返回该person所有removed_at=null的access_B数据。

内容的提问来源于stack exchange，提问作者mhd