PostgreSQL单查询同时获取筛选采购订单及全表email计数方法

单查询实现方案 #

可以通过单条PostgreSQL语句实现需求，以下是两种适配不同场景的写法：

写法1：嵌入子查询（最简洁）

直接将全表计数作为查询字段返回，结果每行都会携带全表的email总计数，若全表email计数≤1时，cnt_email字段返回NULL：

SELECT
    name,
    email,
    (SELECT COUNT(email) FROM purchase_orders HAVING COUNT(email) > 1) AS cnt_email
FROM purchase_orders
WHERE shipment_status > 5 AND shipment_status < 25
ORDER BY name;

写法2：CTE+关联（和原有拆分查询逻辑完全对齐）

如果要求全表email计数≤1时整个查询无返回结果，可以用这种写法，和你之前拆分两条查询的逻辑完全一致：

WITH global_email_stat AS (
    -- 全表统计，仅计数>1时返回结果
    SELECT COUNT(email) AS cnt_email
    FROM purchase_orders
    HAVING COUNT(email) > 1
)
SELECT
    po.name,
    po.email,
    ges.cnt_email
FROM purchase_orders po
CROSS JOIN global_email_stat ges
WHERE po.shipment_status > 5 AND po.shipment_status < 25
ORDER BY po.name;

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