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

我想在表格 View 中显示数据，如下所示:

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

我从 table_b 和 table_d 的每个 trans_id 中获取最新数据，然后运行此查询，但它无法从 table_c 中获取数据，因为 table_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

您可以在 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