SQL Server自连接查询：筛选推荐数少于7的主账户

需求 #

查询推荐数少于7且referralLink不为空的主账户的id和referralLink。

现有错误SQL #

当前使用的SQL会返回所有主账户，且所有结果的推荐数判定值完全相同，语句如下：

select 
      id, 
      referralLink 
   from 
      accs
   where 
      ( 3 > (select 
                   count(*) 
                from 
                   accs as acc 
                      join accs as mainAcc 
                         on mainAcc.id = acc.mainID 
                 where
                    acc.paymentCompleted = 1) 
      and referralLink is not null)

表结构说明 #

表accs包含字段：id（账户ID）、mainID（关联主账户ID，主账户的该字段为空）、referralLink（推荐链接）、paymentCompleted（是否完成支付，1为已完成）。

错误原因 #

子查询未与外层的主账户建立关联，导致子查询计算的是全表中所有已完成支付的子账户总数，而非每个主账户对应的子账户数量。因此所有主账户都用同一个总数进行判断，不符合需求。

修正后的SQL #

写法1：关联子查询 #

适合数据量较小的场景，逻辑直观：

SELECT 
    id, 
    referralLink
FROM 
    accs main_acc
WHERE 
    referralLink IS NOT NULL
    AND (
        SELECT COUNT(*) 
        FROM accs sub_acc
        WHERE sub_acc.mainID = main_acc.id 
          AND sub_acc.paymentCompleted = 1
    ) < 7;

写法2：分组统计后关联 #

适合数据量较大的场景，效率更高：

SELECT 
    main_acc.id, 
    main_acc.referralLink
FROM 
    accs main_acc
LEFT JOIN (
    SELECT mainID, COUNT(*) AS referral_count
    FROM accs
    WHERE paymentCompleted = 1
    GROUP BY mainID
) sub_stats ON main_acc.id = sub_stats.mainID
WHERE 
    main_acc.referralLink IS NOT NULL
    AND COALESCE(sub_stats.referral_count, 0) < 7;

注：COALESCE用于处理没有任何子账户的主账户，此时推荐数记为0，确保这类账户也能被正确筛选。

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