oracle 使用特定条件和附加表 SQL 选择具有最新时间戳的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7836784/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select rows with latest timestamp using certain criteria and additional tables SQL
提问by Greg
I was hoping someone could help me in resolving this issue.
我希望有人能帮助我解决这个问题。
I have 3 tables.
我有3张桌子。
Every table has an ID that I can use to connect all of them together. Those ID's are not the same ones, but they work and connect to each other alright.
每个表都有一个 ID，我可以用它来将所有表连接在一起。这些 ID 不是相同的，但它们可以正常工作并相互连接。
What I need to get is LATEST update date (timestamp) for certain product number (could be more than one product) what was status for it.
我需要得到的是某个产品编号（可能不止一个产品）的最新更新日期（时间戳），它的状态是什么。
Here are how tables look like (they are much more complex, but i simplified them)
这是表格的样子（它们要复杂得多，但我简化了它们）
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))
I tried doing it several different ways, and after searching for a while on the internet, closest i could get to was this:
我尝试了几种不同的方法，在互联网上搜索了一段时间后，我最接近的是：
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
And it returned below which is correct, but when several product numbers are inserted it still returns value for only one product (Which is because of the row number=1probably)
它返回以下正确，但是当插入多个产品编号时，它仍然只返回一个产品的值（这是因为row number=1可能）
PRODUCT_NUMBER DATE_UPDATED STATUS
A555 29/06/2011 07:51:24 30169
Thanks
谢谢
采纳答案by StevieG
This should do it:
这应该这样做：
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)
Ahh, think I misunderstood your data.. Try this instead:
啊，我想我误解了你的数据..试试这个：
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
回答by pilcrow
Might be a good case for analytic functions
可能是解析函数的好例子
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
回答by Marco
Try this:
尝试这个：
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')
