java从2个表中获取每个最新数据，从另一个表中获取数据

Question

0

我有如下数据：

table_a
        ID      CREATED_DATE             PRODUCT
        -----------------------------------------------
        AA      2017-05-19 02:00:00      PHONE
        BB      2017-05-19 02:00:00      CAMERA
        CC      2017-05-19 02:00:00      TELIVISION

table_b
    ID  TRANS_ID  CREATED_DATE             STATUS_ORDER
    ---------------------------------------------------
    1   AA        2017-05-19 02:00:00      WAITING
    2   AA        2017-05-20 02:00:00      IN_PROCESS
    3   BB        2017-05-19 02:00:00      WAITING
    4   CC        2017-05-19 02:00:00      WAITING
    5   CC        2017-05-20 02:00:00      CANCELLED

table_c
    ID  TRANS_ID  CREATED_DATE             STATUS_PAYMENT
    -----------------------------------------------------
    1   BB        2017-05-19 02:00:00      WAITING

 table_d
    ID  TRANS_ID  CREATED_DATE             STATUS_CONTRACT
    -------------------------------------------------------
    1   AA        2017-05-19 02:00:00      IN_PROCESS
    2   AA        2017-05-20 02:00:00      APPROVED
    4   CC        2017-05-19 02:00:00      IN_PROCESS
    5   CC        2017-05-20 02:00:00      REJECT

我想在表视图中显示如下数据：

ID        CREATED_DATE             STATUS_ORDER      STATUS_PAYMENT    STATUS_CONTRACT
--------------------------------------------------------------------------------------------
AA        2017-05-19 02:00:00      IN_PROCESS        NULL              APPROVED
BB        2017-05-19 02:00:00      WAITING           WAITING           NULL
CC        2017-05-19 02:00:00      CANCELLED         NULL              REJECT

我从表b和表d中获取每个trans_id的最新数据，并运行此查询，但它无法从表c获取数据，因为表d上不存在trans_id

SELECT
    a.id,
    a.created_date,
    b.status_order,
    c.status_payment,
    d.status_contract
FROM
    table_a a
    INNER JOIN table_b b ON a.id = b.trans_id
    LEFT OUTER JOIN table_c c ON a.id = c.trans_id
    LEFT OUTER JOIN table_c d ON a.id = d.trans_id
WHERE
    b.created_date = (
        SELECT
            MAX(e.created_date)
        FROM
            table_b e
        WHERE
            b.trans_id = e.trans_id
    )
    AND
    d.created_date = (
        SELECT
            MAX(f.created_date)
        FROM
            table_d f
        WHERE
            f.trans_id = d.trans_id
    )

结果是：

ID    CREATED_DATE           STATUS_ORDER    STATUS_PAYMENT    STATUS_CONTRACT
------------------------------------------------------------------------------
AA    2017-05-19 02:00:00    IN_PROCESS      NULL              APPROVED
CC    2017-05-19 02:00:00    CANCELLED       NULL              REJECT

这并不像预期的那样

Answer 1

像这样的怎么样？遵循代码中的注释。示例数据从第1行到第22行，所以您需要的代码从第23行开始

SQL> with
  2    sample data
  3  table_a(id, created_date, product) as
  4    (select 'aa', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'phone'      from dual union all
  5     select 'bb', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'camera'     from dual union all
  6     select 'cc', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'television' from dual
  7    ),
  8  table_b (id, trans_id, created_date, status_order) as
  9    (select 1, 'aa', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'waiting'    from dual union all
 10     select 2, 'aa', to_date('20.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'in_process' from dual union all
 11     select 3, 'bb', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'waiting'    from dual union all
 12     select 4, 'cc', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'waiting'    from dual union all
 13     select 5, 'cc', to_date('20.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'cancelled'  from dual
 14    ),
 15  table_c (id, trans_id, created_Date, status_payment) as
 16    (select 1, 'bb', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'waiting' from dual),
 17  table_d (id, trans_id, created_date, status_contract) as
 18    (select 1, 'aa', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'in_process' from dual union all
 19     select 2, 'aa', to_date('20.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'approved'   from dual union all
 20     select 3, 'cc', to_date('19.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'in_process' from dual union all
 21     select 4, 'cc', to_date('20.05.2017 02:00', 'dd.mm.yyyy hh24:mi'), 'reject'     from dual
 22    ),
 23  uni as
 24    union a+b+c to have everything in the same "table" (CTE)
 25    (select id trans_id, created_date, null status_order, null status_payment
 26     from table_a
 27     union all
 28     select trans_id, created_date, status_order, null status_payment
 29     from table_b
 30     union all
 31     select trans_id, created_date, null status_order, status_payment
 32     from table_c
 33    ),
 34  latest as
 35    latest date per TRANS_ID
 36    (select trans_id,
 37            max(created_date) created_date
 38     from uni
 39     group by trans_id
 40    )
 41    final query
 42  select l.trans_id,
 43    l.created_date,
 44    max(u.status_order)    status_order,
 45    max(u.status_payment)  status_payment,
 46    max(d.status_contract) status_contract
 47  from latest l
 48       join uni u on u.trans_id = l.trans_id and u.created_date = l.created_date
 49  left join table_d d on d.trans_id = l.trans_id and d.created_date = l.created_date
 50  group by l.trans_id, l.created_Date
 51  order by l.trans_id, l.created_Date;

TR CREATED_DATE     STATUS_ORDER    STATUS_PAYMENT  STATUS_CONTRACT
                  -        -        -
aa 2017-05-20 02:00 in_process                      approved
bb 2017-05-19 02:00 waiting         waiting
cc 2017-05-20 02:00 cancelled                       reject

SQL>

Answer 2

您可以在oracle 18c DB fiddle链接上找到具有所需输出的完整解决方案：https://dbfiddle.uk/?rdbms=oracle_18&fiddle=3f3de8149268cfef58fe7be28106ab3a 我用https://dbfiddle.uk/上提供的数据创建了数据模型，并通过查询和所需的输出解决了问题

查询如下：

 SELECT
  a.id,
  a.created_date,
  b.status_order,
  c.status_payment,
  d.status_contract
FROM table_a a,
(SELECT trans_id,STATUS_order
 FROM (SELECT trans_id,STATUS_order,row_number() OVER (PARTITION BY trans_id ORDER BY created_date DESC) rn 
       FROM table_b) 
 WHERE rn = 1) b,
(SELECT trans_id,STATUS_PAYMENT 
 FROM (SELECT trans_id,STATUS_PAYMENT,row_number() OVER (PARTITION BY trans_id ORDER BY created_date DESC) rn 
       FROM table_c) 
 WHERE rn = 1) c,
(SELECT trans_id,status_contract
 FROM (SELECT trans_id,status_contract,row_number() OVER (PARTITION BY trans_id ORDER BY created_date DESC) rn
      FROM table_d)
 WHERE rn =1) d
WHERE a.id = b.trans_id(+)
AND a.id = c.trans_id(+)
AND a.id = d.trans_id(+);

输出：

ID  CREATED_DATE        STATUS_ORDER    STATUS_PAYMENT  STATUS_CONTRACT
AA  2017-05-19 02:00:00 IN_PROCESS                      APPROVED
BB  2017-05-19 02:00:00 WAITING           WAITING   
CC  2017-05-19 02:00:00 CANCELLED                       REJECT