使用特定条件和附加表 SQL 选择具有最新时间戳的行

Question

我希望有人可以帮助我解决这个问题。

我有3张桌子。

每个表都有一个 ID，我可以用它来将所有表连接在一起。这些 ID 并不相同，但它们可以正常工作并相互连接。

我需要获取的是特定产品编号（可能是多个产品）的最新更新日期（时间戳）以及它的状态。

这是表格的样子（它们要复杂得多，但我简化了它们）

mercury.cs_batch_event
PRODUCT_ID (VARCHAR2(100 CHAR))
CBL_REQUEST_ID (VARCHAR2(12 CHAR))

mercury.cs_status_srch
CBL_REQUEST_ID (VARCHAR2(12 CHAR))
LOG_ID  (VARCHAR2(12 CHAR))
STATUS_REF_ID (NUMBER(8,0))

mercury.edit_log
ELOG_ID (VARCHAR2(12 CHAR))
ON_DATE_TIME (TIMESTAMP(6))

我尝试了几种不同的方式，在互联网上搜索了一段时间后，我能得到的最接近的是：

SELECT * 
FROM (SELECT cbl.product_id, src.status_ref_id,
             TO_CHAR(elog.on_date_time, 'dd/mm/yyyy hh24:mi:ss') date_updated
      FROM mercury.cs_batch_event cbl,
           mercury.cs_status_srch src, 
           mercury.edit_log elog
      WHERE cbl.product_id IN ('A555', 'B555')
        AND cbl.cbl_request_id = src.cbl_request_id
        AND src.log_id = elog.elog_id
      ORDER BY elog.on_date_time DESC)
WHERE rownum = 1

它返回如下 ：正确，但是当插入多个产品编号时，它仍然仅返回一种产品的值（这可能是因为 row number=1 ）

PRODUCT_NUMBER  DATE_UPDATED        STATUS
A555            29/06/2011 07:51:24 30169

谢谢

Answer 1

这应该可以做到：

SELECT 
  t1.product_number, 
  t2.status, 
  TO_CHAR (t3.update_time, 'dd/mm/yyyy hh24:mi:ss') date_updated,
FROM 
  batch_event t1, 
  status_srch t2, 
  edit_log t3
WHERE 
  t1.product_number in ('A555', 'B555')
  AND t1.ID_1 = t2.ID_1
  AND t2.ID_2 = t3.ID_2
  AND t3.update_time = (SELECT max(update_time) 
                        FROM edit_log 
                        WHERE ID_2 = t3.ID_2)

啊，我想我误解了你的数据..试试这个：

SELECT 
  t1.product_number,
  t2.status,
  t3.update_time
FROM 
  batch_event t1, 
  status_srch t2, 
  edit_log t3,
(
    SELECT 
      batch_event.product_number product_number, 
      max(edit_log.update_time) maxupdatetime,
    FROM 
      batch_event, 
      status_srch, 
      edit_log
    WHERE 
      batch_event.ID_1 = status_srch.ID_1
      AND status_srch.ID_2 = edit_log.ID_2
    GROUP BY
      batch_event.product_number
) t4
WHERE t1.ID_1 = t2.ID_1
AND t2.ID_2 = t3.ID_2
AND t1.product_number = t4.product_number
AND t3.update_time = t4.maxupdatetime

Answer 2

可能是分析函数的好例子

SELECT product_id,
       status_ref_id,
       TO_CHAR(on_date_time, 'dd/mm/yyyy hh24:mi:ss')
FROM (SELECT cbl.product_id,
             src.status_ref_id,
             elog.on_date_time,
             ROW_NUMBER() OVER (PARTITION BY cbl.product_id ORDER BY on_date_time DESC)
               AS ordered_by_recency
      FROM mercury.cs_batch_event cbl,
           mercury.cs_status_srch src, 
       mercury.edit_log elog
      WHERE cbl.product_id IN ('A555', 'B555')
        AND cbl.cbl_request_id = src.cbl_request_id
        AND src.log_id = elog.elog_id) d
WHERE ordered_by_recency = 1

Answer 3

试试这个：

SELECT t1.product_number, t2.status,
    (SELECT Update_Time FROM Edit_log el
     INNER JOIN Status_srch ss ON el.ID_2 = ss.ID_2
     INNER JOIN Batch_event be ON ss.ID_1 = be.ID_1
     WHERE be.Product Number = t1.product_number
           AND RowNum<=1
     ORDER BY Update_Time DESC) AS date_updated
FROM Batch_event t1 
INNER JOIN Status_srch t2 ON t1.ID_1 = t2.ID_1
WHERE t1.product_number in ('A555', 'B555')