如何在主查询中引用子查询COUNT值并筛选计数为1的客户

你可以通过以下几种方式实现需求：

方法1：将子查询作为派生表关联（推荐，效率更高） #

先在子查询里过滤出COUNT值为1的客户，再将其作为派生表和主查询关联，主查询可直接引用统计值：

-- 假设主查询从客户主表获取信息，你可以替换成实际的主表/字段
SELECT main.*, sub.ship_count
FROM sysadm.PS_CUST_MAIN main
JOIN (
    SELECT 
        COUNT(SH2.CUST_ID) AS ship_count,
        SH2.CUST_ID
    FROM sysadm.PS_CUST_SHIPTO SH2
    GROUP BY SH2.CUST_ID
    HAVING COUNT(SH2.CUST_ID) = 1 -- 提前过滤，减少关联数据量
) sub ON main.CUST_ID = sub.CUST_ID

方法2：使用关联子查询 #

如果主查询本身基于PS_CUST_SHIPTO表，可在SELECT和WHERE中直接调用关联子查询获取COUNT值：

SELECT 
    SH1.*,
    -- 引用COUNT值作为字段
    (SELECT COUNT(SH2.CUST_ID) 
     FROM sysadm.PS_CUST_SHIPTO SH2 
     WHERE SH2.CUST_ID = SH1.CUST_ID) AS ship_count
FROM sysadm.PS_CUST_SHIPTO SH1
-- 过滤COUNT=1的客户
WHERE (SELECT COUNT(SH2.CUST_ID) 
       FROM sysadm.PS_CUST_SHIPTO SH2 
       WHERE SH2.CUST_ID = SH1.CUST_ID) = 1

注意：这种方式在数据量较大时，子查询会重复执行，效率不如方法1

方法3：使用CTE（公共表表达式） #

用CTE提前计算出符合条件的COUNT值，主查询直接关联CTE即可，代码可读性更强：

WITH ship_counts AS (
    SELECT 
        COUNT(SH2.CUST_ID) AS ship_count,
        SH2.CUST_ID
    FROM sysadm.PS_CUST_SHIPTO SH2
    GROUP BY SH2.CUST_ID
    HAVING COUNT(SH2.CUST_ID) = 1
)
SELECT main.*, sc.ship_count
FROM sysadm.PS_CUST_MAIN main
JOIN ship_counts sc ON main.CUST_ID = sc.CUST_ID

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