如何基于ITEM_ID关联两个独立SQL查询且避免结果相互干扰？
2026-6-16
解决SQL查询中动态日期范围与年度平均值结果失真问题
我编写了SQL查询，用于根据动态日期范围（如2024-10-01至2024-10-28）计算平均值，对应列是SUM(ROUND(QTY_AVG.AVERAGE,2)) DATE_RANGE_AVERAGE。另外有个子查询QTY_AVG_YEAR计算硬编码日期范围的年度平均值，原本想通过QTY_AVG_YEAR.ITEM_ID = rsl.ITEM_ID关联主查询，把年度平均值列放在动态平均值列旁边，但这个关联导致DATE_RANGE_AVERAGE结果失真——因为年度查询引入了全年的更多行数据。需要实现两个独立查询，各自生成不受影响的结果，再基于ITEM_ID合并到同一行。
原SQL代码
select esib.item_number inv_item_id, --:INV_ITEM_ID, pla.item_description descr254_mixed ,SUM(ROUND(QTY_AVG.AVERAGE,2)) DATE_RANGE_AVERAGE --,SUM(ROUND(QTY_AVG_YEAR.AVERAGE_YEAR,2)) YEAR_AVERAGE from po_headers_all pha, po_lines_all pla, po_line_locations_all plla, po_distributions_all pda, rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions rt, egp_system_items_b esib, inv_org_parameters iop, GL_CODE_COMBINATIONS GCC, HR_ALL_ORGANIZATION_UNITS_F_VL HR, INV_UOM_CONVERSIONS INTRACONV , (SELECT SUM(LN.QUANTITY_RECEIVED)/(:P_TO_DATE - :P_FROM_DATE) AVERAGE, SHIPMENT_HEADER_ID, shipment_line_id FROM rcv_shipment_lines LN WHERE LN.CREATION_DATE >= :P_FROM_DATE AND LN.CREATION_DATE <= :P_TO_DATE GROUP BY SHIPMENT_HEADER_ID, shipment_line_id ) QTY_AVG , (SELECT SUM(LN.QUANTITY_RECEIVED)/365 AVERAGE_YEAR, SHIPMENT_HEADER_ID, shipment_line_id, ITEM_ID FROM rcv_shipment_lines LN WHERE TO_CHAR(LN.CREATION_DATE, 'yyyy-mm-dd') >= '2023-10-23' AND TO_CHAR(LN.CREATION_DATE, 'yyyy-mm-dd') <= '2024-09-30' --AND LN.ITEM_ID = :INV_ITEM_ID --rsl.ITEM_ID --AND ITEM_ID =100000030622932 GROUP BY SHIPMENT_HEADER_ID, shipment_line_id, ITEM_ID ) QTY_AVG_YEAR WHERE pha.po_header_id = pla.po_header_id and pla.po_line_id = plla.po_line_id and plla.line_location_id = pda.line_location_id and rt.po_header_id = pha.po_header_id AND transaction_type = 'RECEIVE' AND GCC.CODE_COMBINATION_ID = PDA.CODE_COMBINATION_ID AND pha.SEGMENT1 = NVL(:P_PO_ID, pha.SEGMENT1) AND HR.organization_id = rt.ORGANIZATION_ID AND INTRACONV.UOM_CODE(+) = CASE WHEN esib.UNIT_OF_ISSUE IS NOT NULL THEN esib.UNIT_OF_ISSUE ELSE rsl.UOM_CODE END AND INTRACONV.INVENTORY_ITEM_ID(+) = esib.INVENTORY_ITEM_ID AND QTY_AVG.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID AND QTY_AVG.shipment_line_id = rsl.shipment_line_id AND QTY_AVG_YEAR.ITEM_ID = rsl.ITEM_ID AND YEAR_AVERAGE.ITEM_ID(+) = rsl.ITEM_ID AND rt.transaction_date BETWEEN to_date(nvl( to_char(:P_FROM_DATE,'yyyy-mm-dd') , to_char(sysdate - 30, 'YYYY-MM-DD')) ||' 00:00:00', 'YYYY-MM-DD HH24:Mi:SS') AND to_date(nvl( to_char(:P_TO_DATE,'yyyy-mm-dd') , to_char(sysdate - 1 , 'YYYY-MM-DD')) ||' 23:59:59', 'YYYY-MM-DD HH24:Mi:SS') AND esib.item_number IN ('111090', '112252', '14438', '20', '112399', '110844', '221', '85866', '112304', '219', '112078' ) GROUP BY esib.item_number , pla.item_description
仅保留动态日期范围列的正确结果
INV_ITEM_ID DESCR254_MIXED DATE_RANGE_AVERAGE ------------------------------------------------------------ 219 SALINE, 1000CC IRRIGATION 5.37 221 SALINE, 3000 CC IRRIGATION 5.36 14438 SOLN, SOD CHLOR .9% 250CC 1.64 112252 SOLUTION INTRAVENOUS SODIUM 2.41 112304 SOL IRRIG NACL 0.9% USP 3.56
同时保留两列的错误结果
INV_ITEM_ID DESCR254_MIXED DATE_RANGE_AVERAGE YEAR_AVERAGE ----------------------------------------------------------------------------- 219 SALINE, 1000CC IRRIGATION 1154.55 72.39 221 SALINE, 3000 CC IRRIGATION 766.48 105.1 14438 SOLN, SOD CHLOR .9% 250CC 354.24 30.25 112252 SOLUTION INTRAVENOUS SODIUM 245.82 11.52 112304 SOL IRRIG NACL 0.9% USP 202.92 7.6
解决方案
核心思路是分别计算动态日期范围的平均值和年度平均值，得到两个独立的结果集，再通过ITEM_ID关联合并。
最终SQL代码
WITH date_range_avg AS ( -- 计算动态日期范围的平均值 SELECT esib.item_number inv_item_id, pla.item_description descr254_mixed, SUM(ROUND(QTY_AVG.AVERAGE, 2)) DATE_RANGE_AVERAGE FROM po_headers_all pha JOIN po_lines_all pla ON pha.po_header_id = pla.po_header_id JOIN po_line_locations_all plla ON pla.po_line_id = plla.po_line_id JOIN po_distributions_all pda ON plla.line_location_id = pda.line_location_id JOIN rcv_transactions rt ON rt.po_header_id = pha.po_header_id JOIN rcv_shipment_lines rsl ON rt.shipment_header_id = rsl.shipment_header_id AND rt.shipment_line_id = rsl.shipment_line_id JOIN egp_system_items_b esib ON rsl.item_id = esib.inventory_item_id JOIN GL_CODE_COMBINATIONS GCC ON GCC.CODE_COMBINATION_ID = PDA.CODE_COMBINATION_ID JOIN HR_ALL_ORGANIZATION_UNITS_F_VL HR ON HR.organization_id = rt.ORGANIZATION_ID LEFT JOIN INV_UOM_CONVERSIONS INTRACONV ON INTRACONV.UOM_CODE = CASE WHEN esib.UNIT_OF_ISSUE IS NOT NULL THEN esib.UNIT_OF_ISSUE ELSE rsl.UOM_CODE END AND INTRACONV.INVENTORY_ITEM_ID = esib.INVENTORY_ITEM_ID JOIN ( SELECT SUM(LN.QUANTITY_RECEIVED)/(:P_TO_DATE - :P_FROM_DATE) AVERAGE, SHIPMENT_HEADER_ID, shipment_line_id FROM rcv_shipment_lines LN WHERE LN.CREATION_DATE >= :P_FROM_DATE AND LN.CREATION_DATE <= :P_TO_DATE GROUP BY SHIPMENT_HEADER_ID, shipment_line_id ) QTY_AVG ON QTY_AVG.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID AND QTY_AVG.shipment_line_id = rsl.shipment_line_id WHERE rt.transaction_type = 'RECEIVE' AND pha.SEGMENT1 = NVL(:P_PO_ID, pha.SEGMENT1) AND rt.transaction_date BETWEEN TO_DATE(NVL(TO_CHAR(:P_FROM_DATE,'yyyy-mm-dd'), TO_CHAR(SYSDATE - 30, 'YYYY-MM-DD')) ||' 00:00:00', 'YYYY-MM-DD HH24:Mi:SS') AND TO_DATE(NVL(TO_CHAR(:P_TO_DATE,'yyyy-mm-dd'), TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD')) ||' 23:59:59', 'YYYY-MM-DD HH24:Mi:SS') AND esib.item_number IN ('111090','112252','14438','20','112399','110844','221','85866','112304','219','112078') GROUP BY esib.item_number, pla.item_description ), year_avg AS ( -- 计算年度平均值 SELECT esib.item_number inv_item_id, SUM(ROUND(SUM(LN.QUANTITY_RECEIVED)/365, 2)) YEAR_AVERAGE FROM rcv_shipment_lines LN JOIN egp_system_items_b esib ON LN.item_id = esib.inventory_item_id WHERE LN.CREATION_DATE BETWEEN TO_DATE('2023-10-23', 'yyyy-mm-dd') AND TO_DATE('2024-09-30', 'yyyy-mm-dd') AND esib.item_number IN ('111090','112252','14438','20','112399','110844','221','85866','112304','219','112078') GROUP BY esib.item_number ) -- 合并两个结果集 SELECT dra.inv_item_id, dra.descr254_mixed, dra.DATE_RANGE_AVERAGE, ya.YEAR_AVERAGE FROM date_range_avg dra LEFT JOIN year_avg ya ON dra.inv_item_id = ya.inv_item_id;
关键说明
- 独立计算：两个CTE（
date_range_avg和year_avg）分别计算各自的平均值，避免了原查询中关联年度数据导致的行膨胀，确保结果不受互相干扰。 - 显式JOIN：将原查询的逗号连接语法改为显式
JOIN，逻辑更清晰，减少隐式笛卡尔积的风险。 - LEFT JOIN合并：使用
LEFT JOIN确保即使某些物品没有年度数据，动态日期范围的结果依然能正常显示，不会被过滤掉。
内容的提问来源于stack exchange，提问作者Nick
相关产品推荐
相关产品推荐
