优化前sql
SELECT p.PK_NO PK_NO, p.PICK_CODE PICK_CODE, p.LOGISTIC_CODE LOGISTIC_CODE, p.ROUTE_CODE ROUTE_CODE, p.CAR_CODE CAR_CODE, p.LOGISTIC_USER_NO LOGISTIC_USER_NO, p.LOGISTIC_USER_CODE LOGISTIC_USER_CODE, p.LOGISTIC_NAME LOGISTIC_NAME, p.PICK_USER_NO PICK_USER_NO, p.PICK_NAME PICK_NAME, p.WH_C WH_C, p.STATUS_FLG STATUS_FLG, p.SOURCE_TYPE SOURCE_TYPE, to_char(p.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') CREATE_DATE, brm.route_name, (select count(*) from order_pick_item opi where opi.mas_pk_no = p.pk_no) ordercount, (SELECT sum(oi.UOM_QTY) FROM order_mas om, order_item oi WHERE om.pk_no = oi.mas_pk_no AND om.pk_no IN (select opi.order_pk_no from order_pick_item opi where opi.mas_pk_no = p.pk_no)) orderitemcount, 0 typeValue FROM order_pick p, B2B_ROUTE_MAS brm WHERE 1 = 1 AND p.route_code = brm.route_code AND p.LOGISTIC_CODE = 'GZGYSOP1' AND p.status_flg = 'W' AND p.WH_C = 'GZGYSOP1' 使用with将过滤或处理后的结果先缓存到临时表（此处原理不太清楚，仅代表我的理解），可以大大提高查询效率
优化后sql
with t as (select C.PK_NO,sum(D.UOM_QTY)orderitemcount FROM ORDER_MAS A,order_pick_item B, order_pick C,ORDER_ITEM D WHERE A.pk_no=B.order_pk_no AND B.MAS_PK_NO=C.PK_NO AND A.PK_NO=D.MAS_PK_NO GROUP BY C.PK_NO), t1 as (select p.pk_no,count(*) ordercount from order_pick_item opi,order_pick p where opi.mas_pk_no = p.pk_no group by p.pk_no) SELECT p.PK_NO PK_NO, p.PICK_CODE PICK_CODE, p.LOGISTIC_CODE LOGISTIC_CODE, p.ROUTE_CODE ROUTE_CODE, p.CAR_CODE CAR_CODE, p.LOGISTIC_USER_NO LOGISTIC_USER_NO, p.LOGISTIC_USER_CODE LOGISTIC_USER_CODE, p.LOGISTIC_NAME LOGISTIC_NAME, p.PICK_USER_NO PICK_USER_NO, p.PICK_NAME PICK_NAME, p.WH_C WH_C, p.STATUS_FLG STATUS_FLG, p.SOURCE_TYPE SOURCE_TYPE, to_char(p.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') CREATE_DATE, brm.route_name, t1.ordercount, t.orderitemcount, 0 typeValue FROM order_pick p LEFT JOIN T ON P.PK_NO=T.PK_NO LEFT JOIN T1 ON P.PK_NO=T1.PK_NO, B2B_ROUTE_MAS brm WHERE 1 = 1 AND p.route_code = brm.route_code AND P.PK_NO=T.PK_NO AND P.PK_NO=T1.PK_NO AND p.LOGISTIC_CODE = 'GZGYSOP1' AND p.status_flg = 'W' AND p.WH_C = 'GZGYSOP1'