求助：INGRES数据库中养老基金场景下WHERE NOT EXISTS相关子查询

问题说明 #

涉及两张业务表：

• Table A（账户状态表）：包含字段 subfund_id、units_blocked、register_number、subfund_flag（0表示账户不属于子基金，1表示属于）
• Table B（基金与子基金表）：包含字段 subfund_id、subfund_full_name

需求：筛选出所有存在至少一个冻结单位数大于0的账户的子基金，显示其subfund_id和subfund_full_name。

你编写的SQL代码 #

SELECT
    subfund_id, subfund_full_name
FROM
    Table B as t02
WHERE subfund_flag = 1 AND NOT EXISTS (SELECT *
                FROM Table A as t01
                WHERE t01.subfund_id = t02.subfund_id
                AND  units_blocked = 0)

GROUP BY
    subfund_id, subfund_full_name
HAVING
    COUNT(DISTINCT register_number) >= 1

代码问题分析 #

1. 字段归属错误：subfund_flag是Table A的字段，你直接通过Table B的别名t02引用，会触发语法错误。
2. 逻辑反向：当前NOT EXISTS的条件是"该子基金下没有冻结单位数为0的账户"，这筛选的是所有账户冻结数都大于0的子基金，和需求"存在至少一个冻结数大于0的账户"完全不符。
3. GROUP BY/HAVING无效：Table B的subfund_id应为子基金的唯一标识，GROUP BY后每条记录都是唯一的；且register_number属于Table A，当前关联方式无法正确统计该字段，这部分逻辑冗余且错误。

正确SQL写法 #

方法一：JOIN + DISTINCT（简洁高效） #

SELECT DISTINCT
    t02.subfund_id, t02.subfund_full_name
FROM
    Table B AS t02
JOIN
    Table A AS t01 ON t01.subfund_id = t02.subfund_id
WHERE
    t01.subfund_flag = 1
    AND t01.units_blocked > 0

方法二：IN子查询（逻辑清晰） #

SELECT
    subfund_id, subfund_full_name
FROM
    Table B
WHERE
    subfund_id IN (
        SELECT DISTINCT subfund_id
        FROM Table A
        WHERE subfund_flag = 1
          AND units_blocked > 0
    )

方法三：GROUP BY + HAVING（适配需额外统计的场景） #

SELECT
    t02.subfund_id, t02.subfund_full_name
FROM
    Table B AS t02
JOIN
    Table A AS t01 ON t01.subfund_id = t02.subfund_id
WHERE
    t01.subfund_flag = 1
GROUP BY
    t02.subfund_id, t02.subfund_full_name
HAVING
    SUM(CASE WHEN t01.units_blocked > 0 THEN 1 ELSE 0 END) >= 1

以上三种写法均能准确满足需求，可根据实际业务场景选择。

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