DB2子查询最佳实践

yqkkidmi  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(284)

我需要编写一个DB2查询,该查询执行9个子查询,以便从另一个表中获取状态日期。由于所有子查询的原因,查询运行得非常慢。我想知道是否有更好的方法来实现这一点。有什么建议吗?下面的SQL:

SELECT BILL_NUMBER,

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'ASSGN' and char(ORDER_ID) = BILL_NUMBER) as "FIRST ASSGN",

(SELECT MAX(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'ASSGN' and char(ORDER_ID) = BILL_NUMBER) as "LAST ASSGN", 

(SELECT MAX(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'D-CPICKED' and char(ORDER_ID) = BILL_NUMBER) as "LAST DC PICKED",

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'CONTACTED' and char(ORDER_ID) = BILL_NUMBER) as "FIRST CONTACTED STATUS",

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'UNLOADED' and char(ORDER_ID) = BILL_NUMBER) as "FIRST UNLOADED STATUS",

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'CHECKIN' and char(ORDER_ID) = BILL_NUMBER) as "FIRST CHECKIN STATUS",

(SELECT MIN(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'LHDISP' and char(ORDER_ID) = BILL_NUMBER) as "FIRST LHDISP STATUS",

(SELECT MAX(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'LHDISP' and char(ORDER_ID) = BILL_NUMBER) as "LAST LHDISP STATUS",

(SELECT MAX(Changed) FROM TMWIN.ODRSTAT WHERE STATUS_CODE = 'D-DISP' and char(ORDER_ID) = BILL_NUMBER) as "LAST DDISP STATUS"

FROM TMWIN.TLORDER
WHERE pick_up_by >= '8/1/2021' and pick_up_by <= '8/3/2021'
ctehm74n

ctehm74n1#

使用条件聚合:

SELECT BILL_NUMBER,
       MIN(CASE WHEN OS.STATUS_CODE = 'ASSGN' THEN OS.Changed END) as first_assign,
       MAX(CASE WHEN OS.STATUS_CODE = 'ASSGN' THEN OS.Changed END) as last_assign,
       . . 
FROM TMWIN.TLORDER O JOIN
     TMWIN.ODRSTAT OS
     ON CHAR(OS.ORDER_ID) = O.BILL_NUMBER
WHERE pick_up_by >= DATE '2021-08-01' AND
      pick_up_by <= DATE '2021-08-03'
GROUP BY O.BILL_NUMBER;

相关问题