首先，我先假设你的连接数据存储在一张widget_connections表中，结构大概是这样：

• widget_id：关联到widget的ID
• connection_id：每个连接的唯一标识
• mode：该连接支持的模式（如果一个连接支持多个模式，会对应多行记录；如果是数组存储模式，后面会给出适配写法）

一、基础查询：筛选满足特定模式连接数要求的Widget #

要统计每个Widget对应各模式的独立连接数量（因为一个连接可能支持多种模式，不能重复计数），可以用PostgreSQL的FILTER子句精准统计：

SELECT
    widget_id,
    -- 统计支持模式A的独立连接数
    COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'A') AS mode_a_conn_count,
    -- 统计支持模式B的独立连接数
    COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'B') AS mode_b_conn_count
FROM widget_connections
GROUP BY widget_id
HAVING
    -- 满足至少5个连接支持模式A
    COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'A') >= 5
    -- 且至少10个连接支持模式B
    AND COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'B') >= 10;

如果你的mode字段是数组类型（比如modes text[]），只需要把mode = 'A'改成'A' = ANY(modes)即可：

SELECT
    widget_id,
    COUNT(DISTINCT connection_id) FILTER (WHERE 'A' = ANY(modes)) AS mode_a_conn_count,
    COUNT(DISTINCT connection_id) FILTER (WHERE 'B' = ANY(modes)) AS mode_b_conn_count
FROM widget_connections
GROUP BY widget_id
HAVING
    COUNT(DISTINCT connection_id) FILTER (WHERE 'A' = ANY(modes)) >= 5
    AND COUNT(DISTINCT connection_id) FILTER (WHERE 'B' = ANY(modes)) >= 10;

二、多查询排除已有结果的逻辑 #

如果需要分批次查询，且后续查询要排除之前已经选中的Widget，有几种高效的实现方式：

方式1：使用CTE + EXCEPT（推荐）

用公共表表达式（CTE）定义每一批次的查询条件，通过EXCEPT排除之前的结果，最后可以合并显示各批次的来源：

WITH batch1 AS (
    -- 第一批：满足模式A、B数量要求的Widget
    SELECT widget_id
    FROM widget_connections
    GROUP BY widget_id
    HAVING
        COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'A') >= 5
        AND COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'B') >= 10
),
batch2 AS (
    -- 第二批：满足模式C、D数量要求，但不在第一批结果里的Widget
    SELECT widget_id
    FROM widget_connections
    GROUP BY widget_id
    HAVING
        COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'C') >= 3
        AND COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'D') >= 7
    -- 排除第一批的结果
    EXCEPT
    SELECT widget_id FROM batch1
),
batch3 AS (
    -- 第三批：满足模式E数量要求，但不在前两批结果里的Widget
    SELECT widget_id
    FROM widget_connections
    GROUP BY widget_id
    HAVING
        COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'E') >= 2
    -- 排除前两批的结果
    EXCEPT
    SELECT widget_id FROM batch1
    EXCEPT
    SELECT widget_id FROM batch2
)
-- 合并所有批次，标注所属分组
SELECT 'batch1' AS result_group, widget_id FROM batch1
UNION ALL
SELECT 'batch2' AS result_group, widget_id FROM batch2
UNION ALL
SELECT 'batch3' AS result_group, widget_id FROM batch3;

方式2：使用NOT EXISTS

如果不想用CTE，也可以在后续查询中用NOT EXISTS直接排除已选中的Widget，比如：

-- 第一批结果存入临时表
SELECT widget_id INTO temp_batch1
FROM widget_connections
GROUP BY widget_id
HAVING
    COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'A') >= 5
    AND COUNT(DISTINCT connection_id) FILTER (WHERE mode = 'B') >= 10;

-- 第二批：排除第一批的结果
SELECT widget_id
FROM widget_connections wc
GROUP BY wc.widget_id
HAVING
    COUNT(DISTINCT wc.connection_id) FILTER (WHERE mode = 'C') >= 3
    AND COUNT(DISTINCT wc.connection_id) FILTER (WHERE mode = 'D') >= 7
    AND NOT EXISTS (
        SELECT 1 FROM temp_batch1 b1 WHERE b1.widget_id = wc.widget_id
    );

-- 用完临时表记得删除
DROP TABLE IF EXISTS temp_batch1;

关键注意点 #

• 一定要用COUNT(DISTINCT connection_id)，避免同一个连接因为支持多种模式被重复计数。
• EXCEPT会自动去重，且处理NULL时比NOT IN更安全（NOT IN如果子查询包含NULL会导致结果为空）。
• 如果你的表数据量很大，建议给widget_id、connection_id、mode字段建立联合索引，提升查询效率：

  CREATE INDEX idx_widget_conn_mode ON widget_connections(widget_id, connection_id, mode);
  

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