SQL如何在WHERE语句中统计行数？SQL Server两表关联支付记录数查询问题

方案1：新增子查询（简单直接，适合小数据量） #

直接在你现有查询的基础上，再加一个计算同payment_id总行数的标量子查询即可：

select 
    o.*, 
    -- 满足is_pay=1的行数
    (select count(1) from payment p 
     where p.payment_id = o.payment_id and p.is_pay = 1) as payed_count,
    -- 同payment_id对应的总行数
    (select count(1) from payment p 
     where p.payment_id = o.payment_id) as total_payment_count
from 
    [order] o

注意：order是SQL Server的保留关键字，作为表名使用时建议加方括号[]包裹避免语法报错。

方案2：预聚合后关联（推荐，性能更优） #

如果数据量较大，多次子查询会重复扫描payment表，推荐先对payment表按payment_id分组预计算两个统计值，再和order表关联，只需扫描一次payment表：

select 
    o.*,
    isnull(p.payed_count, 0) as payed_count,
    isnull(p.total_payment_count, 0) as total_payment_count
from [order] o
left join (
    select 
        payment_id,
        sum(case when is_pay = 1 then 1 else 0 end) as payed_count,
        count(*) as total_payment_count
    from payment
    group by payment_id
) p on o.payment_id = p.payment_id

该方案还兼容payment表中不存在对应payment_id的场景，用isnull将空值转为0，避免返回null统计值。

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