加入表格中的最大日期 [英] Joining with max date from table

问题描述

SELECT COL1,
    COL2,
    COL3
FROM TABLE1,
    TABLE2,
    TABLE3,
    TABLE4
WHERE TABLE1.KEY1 = TABLE2.KEY1
    AND TABLE2.KEY = TABLE3.KEY
    AND TABLE2.FILTER = 'Y'
    AND TABLE3.FILTER = 'Y'
    AND TABLE2.KEY = TABLE3.KEY
    AND TABLE3.KEY = TABLE4.KEY

我有一个类似的查询，我需要进行修改，在表3中有一个date列，我需要选择日最高值行来加入.可以说表3中有4行对联接感到满意，我需要从这4个表中选择最高日期的行以进行联接，然后显示结果.

I have a similar query and I need to do modification, in a table 3 there is a date column and I need to pick highest day value row for joining. Lets say there are 4 rows from table number 3 which are getting satisfied for join, I need to pick highest date row out of those 4 for joining purpose and then show the result.

希望问题很明确.数据库Oracle 10g

Hope question is clear. Database oracle 10g

推荐答案

尝试类似此查询的操作.

Try something like this query.

SELECT
    COL1,
    COL2,
    COL3,
    T33.*

FROM TABLE1

JOIN TABLE2 ON TABLE1.KEY1 = TABLE2.KEY1

JOIN TABLE4 ON TABLE2.KEY = TABLE4.KEY

JOIN

(
    SELECT MAX(T.Day) as DT, T.KEY
    FROM TABLE3 T
    WHERE T.FILTER = 'Y'
    GROUP BY T.KEY
) T3 on TABLE4.KEY = T3.KEY

JOIN TABLE3 T33 ON T3.KEY = T33.KEY AND T3.DT = T33.Day

WHERE

TABLE2.FILTER = 'Y'

主要思想是代替
加入TABLE3，您可以执行以下操作:

The main idea is that instead of
joining to TABLE3 you do this:

SELECT MAX(T.Day) as DT, T.KEY
FROM TABLE3 T
WHERE T.FILTER = 'Y'
GROUP BY T.KEY

为该表/记录设置一个名称，然后加入该名称.

give that table/recordset a name and join to it instead.

然后您可以再次加入原始的TABLE3(请参阅T33)
从TABLE3中拉出所有其他需要的列，这些列是
T3中不存在.

Then you can join again to the original TABLE3 (see T33)
to pull all the other needed columns from TABLE3 which are
not present in T3.

我想您可以确定其他细节.

You can work out the other details, I think.

这篇关于加入表格中的最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！