如何基于多个表从Oracle结果集中删除重复项

Question

我有两个表：一个主要的应用程序表，保存核心数据，和一个状态跟踪表，反映应用程序表中核心数据的状态变化。

应用程序表和状态跟踪表之间存在一种1:N的关系。以下是示例：

-- APPLICATION
SELECT A.ID, A.DECISIONON, A.APPLICATIONSTATUS_ID AS CURRENTSTATUS
FROM APPLICATION A
WHERE A.ID=1099;

Results:
ID      DECISIONON  CURRENTSTATUS
1099    5/05/2009   5

-- STATUS TRACKING
SELECT ST.ID, ST.APPLICATION_ID AS APP_ID, ST.APPLICATIONSTATUS_ID AS STATUS, ST.CREATEDATE 
FROM STATUSTRACKING ST
WHERE ST.APPLICATION_ID=1099
ORDER BY ST.CREATEDATE DESC;

Results:
ID      APP_ID  STATUS  CREATEDATE
44466   1099    5       5/05/2009
44458   1099    7       5/05/2009
10826   1099    8       21/07/2008
9770    1099    7       9/07/2008
4410    1099    3       9/05/2008
3814    1099    2       2/05/2008
3803    1099    1       2/05/2008

问题： 我需要选择特定状态下最近日期的STATUSTRACKING记录。这是我的尝试：

SELECT A.ID AS APP_ID, A.APPLICATIONSTATUS_ID AS CURR_ST, Z.ID AS ST_ID, Z.CREATEDATE, Z.APPLICATIONSTATUS_ID AS OLD_ST
FROM APPLICATION A, STATUSTRACKING Z
WHERE A.APPLICATIONSTATUS_ID in (5,6)
    AND A.ID IN (337,1099,1404,9441)
    AND Z.APPLICATIONSTATUS_ID = 7
    AND Z.APPLICATION_ID=A.ID
ORDER BY A.ID ASC, Z.CREATEDATE DESC;

Results:
APP_ID  CURR_ST ST_ID       CREATEDATE  OLD_ST
337     6       13978       17/08/2008  7
1099    5       44458       5/05/2009   7
1099    5       9770        9/07/2008   7
1404    6       15550       28/08/2008  7
9441    5       49271       3/06/2009   7
9441    5       46058       13/05/2009  7

问题在于有重复的行。我需要只显示最近创建时间的行。以 App_ID 1099 为例，应该只显示以下记录：

1099    5       44458       5/05/2009   7

我很明显不想排除那些没有重复的行。

我认为这个语句可以给出我要找的行：

SELECT A.ID, A.APPLICATION_ID, A.APPLICATIONSTATUS_ID, A.CREATEDATE
FROM    (SELECT * 
            FROM STATUSTRACKING 
            WHERE APPLICATIONSTATUS_ID=7 
                AND APPLICATION_ID=1099 
            ORDER BY CREATEDATE DESC) A
WHERE ROWNUM = 1;

...但是我似乎无法让它与我的主要选择语句一起工作。

我想要的结果集应该是这样的:

APP_ID  CURR_ST ST_ID       CREATEDATE  OLD_ST
337     6       13978       17/08/2008  7
1099    5       44458       5/05/2009   7
1404    6       15550       28/08/2008  7
9441    5       49271       3/06/2009   7
etc. ...

我不是Oracle/SQL专家，所以需要您的帮助。

- user1058946

Answer 1

最简单的方法可能是使用分析函数。类似这样：

SELECT *
  FROM (
    SELECT A.ID AS APP_ID, 
           A.APPLICATIONSTATUS_ID AS CURR_ST, 
           Z.ID AS ST_ID, 
           Z.CREATEDATE, 
           Z.APPLICATIONSTATUS_ID AS OLD_ST,
           rank() over (partition by a.id order by z.createDate desc) rnk
      FROM APPLICATION A, 
           STATUSTRACKING Z
     WHERE A.APPLICATIONSTATUS_ID in (5,6)
       AND A.ID IN (337,1099,1404,9441)
       AND Z.APPLICATIONSTATUS_ID = 7
       AND Z.APPLICATION_ID=A.ID
    )
 WHERE rnk = 1

如果有可能存在并列的情况，你可能会想要使用 ROW_NUMBER 或 DENSE_RANK 分析函数而不是 RANK。

- Justin Cave

---

太棒了！这个可以直接使用，不需要修改。感谢您的教导。 - user1058946

Answer 2

如果我理解正确，您想要应用程序的最新状态，对吗？问题是：您的创建日期是否也包括时间（小时、分钟和秒）？也许这个查询可以帮助您：

select a.* , st.*
  from application a
     , statustracking st
 where a.id = st.app_id
   and st.createdate = ( select max(st1.createdate)
                           from statustracing st1
                          where st1.app_id = a.id
                            and st1.application_statusid = st.application_statusid  )
   -- filters goes there
   and a.id in (....)
   and st.application_statusid in (...)

- user800014

---

谢谢您。稍加调整以匹配我的语法，这个解决方案也可以使用！谢谢。 - user1058946