SQL查询优化：按唯一ID_PROD汇总实际重量并计算库存值

现有关联查询会返回同一ID_PROD的多条重复记录，其中THEORETICAL_WEIGHT对唯一ID_PROD为固定值，REAL_WEIGHT为单条生产记录的实际重量。

原查询语句 #

SELECT 
    A.ID_PROD,  A.REAL_WEIGHT, B.THEORETICAL_WEIGHT, 
    A.REAL_WEIGHT * B.THEORETICAL_WEIGHT AS Stock
FROM 
    Table_A A,   
    Table_B B
WHERE 
    B.ID_PROD = A.ID_PROD;

原查询结果 #

ID_PROD | REAL_WEIGHT | THEORETICAL_WEIGHT | Stock
 P01         1,5               3              4,5
 P01         0,3               3              0,9
 P01         5,1               3              15,3
 P02         2,1               2              4,2
 P02          3                2              6        
 P03          4                6              24

优化后查询语句 #

推荐两种实现方式，按需选择：

方式一：直接聚合关联

利用GROUP BY按ID_PROD和固定的THEORETICAL_WEIGHT分组，计算实际重量总和：

SELECT 
    A.ID_PROD,
    SUM(A.REAL_WEIGHT) AS REAL_WEIGHT,
    B.THEORETICAL_WEIGHT,
    SUM(A.REAL_WEIGHT) * B.THEORETICAL_WEIGHT AS Stock
FROM 
    Table_A A
INNER JOIN 
    Table_B B ON B.ID_PROD = A.ID_PROD
GROUP BY 
    A.ID_PROD, B.THEORETICAL_WEIGHT;

方式二：先聚合再关联（性能更优）

先通过CTE聚合Table_A的实际重量总和，再关联Table_B获取理论重量，减少关联数据量：

WITH Aggregated_RealWeight AS (
    SELECT 
        ID_PROD,
        SUM(REAL_WEIGHT) AS TOTAL_REAL_WEIGHT
    FROM Table_A
    GROUP BY ID_PROD
)
SELECT 
    ARW.ID_PROD,
    ARW.TOTAL_REAL_WEIGHT AS REAL_WEIGHT,
    B.THEORETICAL_WEIGHT,
    ARW.TOTAL_REAL_WEIGHT * B.THEORETICAL_WEIGHT AS Stock
FROM Aggregated_RealWeight ARW
INNER JOIN Table_B B ON ARW.ID_PROD = B.ID_PROD;

预期结果 #

ID_PROD | REAL_WEIGHT | THEORETICAL_WEIGHT | Stock
   P01         6,9               3            20,7
   P02         5,1               2            10,2      
   P03          4                6            24

（注：原预期结果中P02的REAL_WEIGHT应为2.1+3=5.1，此处修正笔误）

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