SQL多表查询：统计expiryamt大于对应orderamt 2%的记录数与总金额

实现思路 #

• 采用条件聚合实现需求：通过CASE WHEN筛选出满足supexpiry.expiryamt大于关联suporder.orderamt 2%的记录，再分别统计条数和对应总金额
• 注意原SQL中supexpiry的金额字段名需和实际表结构对齐：需求中字段名为expiryamt，原SQL写为expiredamt，请根据实际情况调整

修改后完整SQL #

select 
    s.merchantId::int as merchant,
    s.merchantName as merchantname,
    count(s.id)::int as ordercount,
    sum(s.orderamt) as orderamt,
    count(s2.orderid)::int as expirycount,
    sum(s2.expiryamt) as expiryamt,
    -- 新增：过期金额超订单金额2%的记录数
    count(case when s2.expiryamt > s.orderamt * 0.02 then 1 end)::int as "expiry>2%count",
    -- 新增：符合条件的过期金额总和
    sum(case when s2.expiryamt > s.orderamt * 0.02 then s2.expiryamt else 0 end) as "expiry>2%amt"
from suporder s 
left join supexpiry s2 on s2.orderid = s.id
where s.orderdate >= '2021-09-01'::date and s.orderdate <= '2021-10-13'::date
group by s.merchantid, s.merchantname 

补充说明 #

• 原SQL开头的distinct可以省略：因为已经按商户ID和商户名称分组，每组本身就是唯一商户，不会有重复值
• CASE WHEN未匹配到符合条件的记录时会返回null，count统计时会自动忽略null值，符合计数需求
• 金额比较的阈值0.02可根据业务需求灵活调整

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