多表查询求和重复问题解决————且一同解决了int类型减去null类型出现null的问题！！！

最新推荐文章于 2025-07-16 13:41:18 发布

原创最新推荐文章于 2025-07-16 13:41:18 发布 · 330 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一种解决SQL中A、B、C三表联查时A表数据重复的问题，通过子查询及多层SELECT DISTINCT实现正确求和，避免了数据重复带来的错误结果，适用于一对多及一对一表关系。

有A、B、C三表表与表关系为 A–>B 为一对多 A–>C为一对一三表联查求和会出现 A表数据重复问题解决办法子查询！！三层 SELECT DISTINCT 亲测有效

    SELECT DISTINCT (q.id),q.code,q.name,q.batch,SUM(q.total),(ISNULL(SUM(q.total),0)-ISNULL(q.total2, 0))AS total3 FROM
    (SELECT DISTINCT (w.id),w.code,w.name,w.batch,w.num as total,sum(w.num2)as total2  FROM(
    SELECT DISTINCT (so.id)AS oid, m.id,m.code,m.name,so.batch,so.num as num,oo.num as num2
    FROM base_storage_outrecord AS so
    LEFT JOIN base_material AS m
    ON so.mid = m.id
    LEFT JOIN busness_product_order_outdata AS oo
    ON so.orderno = oo.orderno 
    AND so.batch = oo.bacth
    AND so.mid = oo.mid
    WHERE  so.orderno = 1109987621 AND so.batch = 1)AS w
    GROUP BY w.id,w.code,w.name,w.batch,w.num)AS q
    GROUP BY  q.id,q.code,q.name,q.batch,q.total

在这里插入图片描述