DB2子查询最佳实践

yqkkidmi 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(264)

我需要编写一个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'

db2

来源：https://stackoverflow.com/questions/69033900/db2-subquery-best-practice

1条答案

按热度按时间

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;

赞(0）回复(0）举报 2022-11-07

我来回答

DB2子查询最佳实践

1条答案

相关问题

热门标签

最新问答