SQL查询改写：按ID_PROD聚合求和并应用指定筛选条件

原查询语句 #

SELECT 
  D.ID_STORAGE,
  D.QUIN, 
  A.ID_PROD AS ID_PROD_FULL,
  SUBSTR(A.ID_PROD, 1, 4) AS ID_PROD,
  B.PROD_GROUP,
  A.REAL_WEIGHT,
  C.VALK,
  C.REV,
  C.STORAGE_TYPE,
  B.THEORETICAL_WEIGHT,
  B.THEORETICAL_WEIGHT * A.REAL_WEIGHT AS FINAL_WEIGHT
FROM 
  Table_a A,
  Table_b B,
  Table_c C,
  Table_d D
WHERE 
  C.STORAGE_TYPE = D.STORAGE_TYPE
  AND C.ID_STORAGE = D.ID_STORAGE
  AND C.ID_LINE = D.ID_LINE
  AND A.ID_PROD = C.ID_PROD
  AND B.ID_PROD = C.ID_PROD
  AND B.ID_PROD = A.ID_PROD
  AND A.NRT = C.NRT;

原查询返回结果 #

改写需求 #

需要实现每个ID_PROD仅显示一行，包含以下内容：

• 该ID_PROD的REAL_WEIGHT总和
• 该ID_PROD对应的唯一THEORETICAL_WEIGHT
• 总和与理论重量相乘得到的FINAL_WEIGHT

同时仅对符合以下筛选条件的行进行计算：

• STORAGE_TYPE 为 KO、TO、Z
• PROD_GROUP 为 NM、Z、S
• C.VALK 为 J、C、D、E
• ID_STORAGE 为 HESZ、FRES
• D.QUIN 为 Z、K
• C.REV 为 X、Y、H

期望结果 #

解决方案 #

改写后的SQL语句 #

SELECT 
  SUBSTR(A.ID_PROD, 1, 4) AS ID_PROD,
  SUM(A.REAL_WEIGHT) AS REAL_WEIGHT,
  MAX(B.THEORETICAL_WEIGHT) AS THEORETICAL_WEIGHT,
  SUM(A.REAL_WEIGHT) * MAX(B.THEORETICAL_WEIGHT) AS FINAL_WEIGHT
FROM 
  Table_a A
JOIN Table_c C ON A.ID_PROD = C.ID_PROD AND A.NRT = C.NRT
JOIN Table_b B ON B.ID_PROD = C.ID_PROD
JOIN Table_d D ON C.STORAGE_TYPE = D.STORAGE_TYPE 
              AND C.ID_STORAGE = D.ID_STORAGE 
              AND C.ID_LINE = D.ID_LINE
WHERE 
  -- 筛选条件
  C.STORAGE_TYPE IN ('KO', 'TO', 'Z')
  AND B.PROD_GROUP IN ('NM', 'Z', 'S')
  AND C.VALK IN ('J', 'C', 'D', 'E')
  AND D.ID_STORAGE IN ('HESZ', 'FRES')
  AND D.QUIN IN ('Z', 'K')
  AND C.REV IN ('X', 'Y', 'H')
GROUP BY 
  SUBSTR(A.ID_PROD, 1, 4);

说明 #

1. 显式JOIN替换隐式连接：将原查询的逗号分隔表写法改为显式JOIN，逻辑更清晰，可读性更强。
2. 分组聚合：通过GROUP BY SUBSTR(A.ID_PROD, 1, 4)按ID_PROD分组，用SUM(A.REAL_WEIGHT)计算符合条件的重量总和。
3. 获取唯一理论重量：由于每个ID_PROD对应的THEORETICAL_WEIGHT是唯一值，用MAX(B.THEORETICAL_WEIGHT)确保正确获取该值（用MIN结果一致）。
4. 计算最终重量：直接用重量总和乘以理论重量得到FINAL_WEIGHT。
5. 筛选条件整合：所有筛选条件统一加入WHERE子句，确保仅处理符合要求的数据。

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