SQL查询链接表，从一个表返回最早的历史记录和相关结果

Question

我有两个需要查询的表。我们称它们为表A和表A_HISTORIES。表A中的每一行都链接到A_HISTORIES中的多行。我想做的是能够将表A中的每一行与表A_HISTORIES中最早的历史记录链接起来，例如:-

SELECT A.*
       A_HISTORIES.CREATED_DATE
FROM   A, A_HISTORIES
WHERE  A.ID = A_HISTORIES.A_ID
AND    A_HISTORIES.ID = (SELECT  max(id) keep (dense_rank first order by CREATED_DATE) 
                         FROM    A_HISTORIES)

但是，这将只返回A/A_HISTORIES中具有最早的CREATED_DATE的行。有人能帮我做一下A中的每一行吗?由于

Answer 1

这样如何:

SELECT A.*
       A_HISTORIES.CREATED_DATE
FROM   A
  INNER JOIN A_HISTORIES ON A.ID = A_HISTORIES.A_ID
  INNER JOIN (SELECT A_ID, MAX(CREATE_DATE) AS max_create_date
              FROM A_HISTORIES
              GROUP BY A_ID) max_hist ON A_HISTORIES.A_ID = max_hist.A_ID 
                                     AND A_HISTORIES.ceate_date = max_create_date