SQL查询执行耗时过长且返回重复数据问题求助

嘿，我看你现在的SQL查询遇到了不少麻烦——耗时过长、返回重复数据，甚至中途被迫终止，核心需求是按客户账户统计对应产品的数量，而且产品和账户是一对一关系对吧？咱们先拆解下当前SQL的问题，再一步步优化。

当前SQL的核心问题 #

1. 未聚合的明细JOIN导致笛卡尔积：你用了多个LEFT JOIN关联未聚合的明细子查询（比如vGRN、vPack1/2/3），一个客户如果在不同模块有多个记录，JOIN后就会产生大量重复行，数据量直接爆炸，这不仅导致重复结果，还拖垮了性能。
2. OR关联条件拖慢查询：关联产品表时用了vProd.iMasterId=vGRN.Item OR vProd.iMasterId=vDel.Item OR ...这种OR条件，数据库没法有效利用索引，而且会进一步放大匹配的行数，雪上加霜。
3. 没有提前做统计聚合：直接用明细数据做计算，数据库要处理的行数太多，内存和CPU扛不住，自然会中途终止。

优化思路：先聚合，再关联 #

咱们换个思路：先对每个业务模块（GRN、包装、发货）按客户+产品做统计聚合，把明细数据压缩成每个客户-产品对的单行统计结果，再把这些聚合后的结果关联起来。同时利用产品和账户的一对一关系，先构建出所有客户-产品的唯一组合，确保最终结果不会重复。

优化后的SQL示例 #

WITH 
-- 1. 先聚合GRN模块的各类数量，按客户+产品分组
agg_grn AS (
    SELECT 
        a.Customer AS CustomerId,
        b.Item AS ProductId,
        SUM(CASE WHEN a.Status='GRN' THEN b.Qty ELSE 0 END) AS GRN,
        SUM(CASE WHEN a.Status='OTH' THEN b.Qty ELSE 0 END) AS OTH_GRN,
        SUM(CASE WHEN a.Status='CNV' THEN b.Qty ELSE 0 END) AS CNV_GRN
    FROM vCore_GRN a
    INNER JOIN vuCore_GRN_Product_Details_Details b ON b.iMasterId = a.iMasterId
    GROUP BY a.Customer, b.Item
),
-- 2. 聚合包装模块的原材料数量
agg_pack_raw AS (
    SELECT 
        a.CustomerName AS CustomerId,
        b.Items AS ProductId,
        SUM(b.Qty) AS RawMaterial
    FROM vCore_Packaging a
    INNER JOIN vuCore_Packaging_Packaging_Details_Details b ON a.iMasterId = b.iMasterId
    WHERE a.iMasterId > 0
    GROUP BY a.CustomerName, b.Items
),
-- 3. 聚合包装模块的包装件数量
agg_pack_package AS (
    SELECT 
        a.CustomerName AS CustomerId,
        b.Product AS ProductId,
        SUM(b.PQty) AS PackageItem
    FROM vCore_Packaging a
    INNER JOIN vuCore_Packaging_Packing_Items_Details b ON a.iMasterId = b.iMasterId
    WHERE a.iMasterId > 0
    GROUP BY a.CustomerName, b.Product
),
-- 4. 聚合包装模块的成品数量
agg_pack_finished AS (
    SELECT 
        a.CustomerName AS CustomerId,
        b.Product AS ProductId,
        SUM(b.FGQty) AS FinishedGoods
    FROM vCore_Packaging a
    INNER JOIN vuCore_Packaging_Finished_Godds_Details b ON a.iMasterId = b.iMasterId
    WHERE a.iMasterId > 0
    GROUP BY a.CustomerName, b.Product
),
-- 5. 聚合发货模块的各类数量
agg_delivery AS (
    SELECT 
        a.CustomerName AS CustomerId,
        b.Item AS ProductId,
        SUM(CASE WHEN a.Status='DN' THEN b.Qty ELSE 0 END) AS DN,
        SUM(CASE WHEN a.Status='CNV' THEN b.Qty ELSE 0 END) AS CNV_DN,
        SUM(CASE WHEN a.Status='OTH' THEN b.Qty ELSE 0 END) AS OTH_DN
    FROM vCore_DeliveryNotes a
    INNER JOIN vuCore_DeliveryNotes_Product_Details_Details b ON b.iMasterId = a.iMasterId
    GROUP BY a.CustomerName, b.Item
),
-- 6. 构建客户-产品的唯一组合（这里要注意：如果产品和账户是一对一，应该有明确的关联字段，比如产品表的CustomerId）
customer_products AS (
    SELECT 
        vAcc.iMasterId AS CustomerId,
        vAcc.sName AS CustomerName,
        vProd.iMasterId AS ProductId,
        vProd.sName AS ProductName
    FROM vCore_Account vAcc
    INNER JOIN vCore_Product vProd 
        -- 替换成你实际的一对一关联字段，原SQL的OR逻辑不准确，建议用明确的关联
        ON vProd.CustomerId = vAcc.iMasterId
    WHERE vAcc.iMasterId > 0
)
-- 最后把所有聚合结果关联到唯一的客户-产品组合上
SELECT 
    cp.CustomerName,
    cp.ProductName,
    COALESCE(ag.GRN, 0) AS GRN,
    COALESCE(ag.OTH_GRN, 0) AS OTH,
    COALESCE(ag.CNV_GRN, 0) AS CNV_GRN,
    COALESCE(apr.RawMaterial, 0) AS RawMaterial,
    COALESCE(app.PackageItem, 0) AS PackageItem,
    COALESCE(apf.FinishedGoods, 0) AS FinishedGoods,
    COALESCE(ad.DN, 0) AS DN,
    COALESCE(ad.CNV_DN, 0) AS CNV_DN,
    COALESCE(ad.OTH_DN, 0) AS OTH_DN
FROM customer_products cp
LEFT JOIN agg_grn ag ON cp.CustomerId = ag.CustomerId AND cp.ProductId = ag.ProductId
LEFT JOIN agg_pack_raw apr ON cp.CustomerId = apr.CustomerId AND cp.ProductId = apr.ProductId
LEFT JOIN agg_pack_package app ON cp.CustomerId = app.CustomerId AND cp.ProductId = app.ProductId
LEFT JOIN agg_pack_finished apf ON cp.CustomerId = apf.CustomerId AND cp.ProductId = apf.ProductId
LEFT JOIN agg_delivery ad ON cp.CustomerId = ad.CustomerId AND cp.ProductId = ad.ProductId
ORDER BY cp.CustomerName, cp.ProductName;

关键优化点解释 #

• 提前聚合压缩数据：每个CTE都先按客户+产品分组统计，把原来的N条明细变成1条聚合记录，彻底避免JOIN时的笛卡尔积，数据量直接大幅减少。
• 明确的关联键：用CustomerId + ProductId作为关联条件，替代原SQL的OR逻辑，数据库可以利用索引快速匹配，查询速度提升明显。
• 唯一组合打底：先构建客户-产品的唯一组合表，确保最终结果严格符合你“一对一”的业务规则，不会出现重复行。
• COALESCE处理空值：把LEFT JOIN带来的NULL转换成0，保证统计数值的完整性，不用再处理空值问题。

额外的性能小 tips #

• 检查下vCore_GRN.Customer、vuCore_GRN_Product_Details_Details.Item、vCore_Packaging.CustomerName这些关联字段有没有加索引，没有的话赶紧建，能进一步加速查询。
• 原SQL里vCore_Packaging的子查询都加了a.iMasterId>0，如果这个条件能过滤掉大量无效数据就保留，过滤效果差的话可以考虑移除。
• 如果客户和产品的一对一关系是通过中间表关联的，记得调整customer_products CTE里的关联逻辑，确保能准确获取所有客户对应的产品。

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