[oracle]SQL 选择跨多个表与每个联接的表中的最大日期

标签: sql Oracle
发布时间: 2017/4/15 18:28:54
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有一个 Oracle 11 SQL 查询在哪里我在停车从 4 表中的所有 id 加入数据3 表联接到每个有需要 'Y' 和有效日期 (EFF_DATE) 的 ACTIVE_IND 值的第一次,我需要从每个最高有效日期的记录。

我有一个查询,在这里工作,但想知道是否有一更优化/有效的它看起来有点慢的写作方式。 目前我在使用看上去有点像这样︰

SELECT DISTINCT A.TABLE1_ID,
                A.TABLE1_COL1,
                B.TABLE2_COL2,
                C.TABLE3_COL3,
                D.TABLE4_COL4
FROM TABLE1 A,
     TABLE2 B,
     TABLE3 C,
     TABLE4 D
WHERE B.TABLE2_ID = A.TABLE1_ID
  AND B.TABLE2_ACTIVE_IND = 'Y'
  AND B.TABLE2_EFF_DATE =
    (SELECT MAX (B2.TABLE2_EFF_DATE)
     FROM TABLE2 B2
     WHERE B2.TABLE2_ID = B.TABLE2_ID
       AND B2.TABLE2_ACTIVE_IND = 'Y')
  AND C.TABLE3_ID = A.TABLE1_ID
  AND C.TABLE3_ACTIVE_IND = 'Y'
  AND C.TABLE3_EFF_DATE =
    (SELECT MAX (C2.TABLE3_EFF_DATE)
     FROM TABLE3 C2
     WHERE C2.TABLE3_ID = C.TABLE3_ID
       AND C2.TABLE3_ACTIVE_IND = 'Y')
  AND D.TABLE4_ID = A.TABLE1_ID
  AND D.TABLE4_ACTIVE_IND = 'Y'
  AND D.TABLE4_EFF_DATE =
    (SELECT MAX (D2.TABLE4_EFF_DATE)
     FROM TABLE4 D2
     WHERE D2.TABLE4_ID = D.TABLE4_ID
       AND D2.TABLE4_ACTIVE_IND = 'Y')
ORDER BY A.TABLE1_ID;

解决方法 1:

请尝试︰

SELECT DISTINCT A.TABLE1_ID,
                A.TABLE1_COL1,
                B.TABLE2_COL2,
                C.TABLE3_COL3,
                D.TABLE4_COL4
FROM TABLE1 A
JOIN (SELECT T2.*, 
             RANK() OVER (PARTITION BY TABLE2_ID ORDER BY TABLE2_EFF_DATE DESC) RN
      FROM TABLE2 T2
      WHERE TABLE2_ACTIVE_IND = 'Y') B ON B.TABLE2_ID = A.TABLE1_ID AND B.RN=1
JOIN (SELECT T3.*, 
             RANK() OVER (PARTITION BY TABLE3_ID ORDER BY TABLE3_EFF_DATE DESC) RN
      FROM TABLE3 T3
      WHERE TABLE3_ACTIVE_IND = 'Y') C ON C.TABLE3_ID = A.TABLE1_ID AND C.RN=1
JOIN (SELECT T4.*, 
             RANK() OVER (PARTITION BY TABLE4_ID ORDER BY TABLE4_EFF_DATE DESC) RN
      FROM TABLE4 T4
      WHERE TABLE4_ACTIVE_IND = 'Y') D ON D.TABLE4_ID = A.TABLE1_ID AND D.RN=1
ORDER BY A.TABLE1_ID;
官方微信
官方QQ群
31647020