甲骨文SQL.选择最大值(日期)

Question

需要更新表中的值。本质上，我添加了fromDate和toDate列。我在价格日期设置的值：

UPDATE schema.myTable SET FROMDATE = PRICEDATE, TODATE = PRICEDATE WHERE FROMDATE IS NULL AND TODATE IS NULL

这行得通。然后，我需要将最新日期设置为 6000-01-01。所有行都是唯一的，但可以与类似的对象相关。所以我可能有：

priceDate | fromDate | toDate | INSTRUMENT | uniqueID 25/09/2018 25/09/2018 25/09/2018 AUD | 1 26/09/2018 26/09/2018 26/09/2018 EUR | 2 25/09/2018 25/09/2018 25/09/2018 EUR | 3 25/09/2018 25/09/2018 25/09/2018 JPN | 4 29/09/2018 29/09/2018 29/09/2018 EUR | 5 29/09/2018 29/09/2018 29/09/2018 JPN | 6 20/09/2018 20/09/2018 20/09/2018 JPN | 7 28/09/2018 28/09/2018 28/09/2018 AUD | 8

我试过：UPDATE schema.myTable SET TODATE = TO_DATE('60000101', 'yyyymmdd') WHERE TODATE in ( SELECT MAX(TODATE) FROM schema.myTable WHERE HISTTYPE = 'Close' GROUP BY INSTRUMENT );

但这给了我多个相同的工具，我只想要一个。 编辑：

所以在我的查询之后，我想要：

priceDate | fromDate | toDate | INSTRUMENT | uniqueID 25/09/2018 25/09/2018 25/09/2018 AUD | 1 26/09/2018 26/09/2018 26/09/2018 EUR | 2 25/09/2018 25/09/2018 25/09/2018 EUR | 3 25/09/2018 25/09/2018 25/09/2018 JPN | 4 29/09/2018 29/09/2018 01/01/6000 EUR | 5 29/09/2018 29/09/2018 01/01/6000 JPN | 6 20/09/2018 20/09/2018 20/09/2018 JPN | 7 28/09/2018 28/09/2018 01/01/6000 AUD | 8

Answer 1

我认为你只需要一个相关的子查询：

UPDATE schema.myTable t
SET TODATE = TO_DATE('60000101', 'yyyymmdd') 
WHERE TODATE = ( SELECT MAX(tt.TODATE)
FROM schema.myTable tt
WHERE tt.HISTTYPE = 'Close' AND
tt.INSTRUMENT = t.INSTRUMENT
);

Answer 2

如果理解正确，那么您可以使用row_number窗口函数并找到每组 INSTRUMENT 的最大日期，然后更新这些

SET TODATE = TO_DATE('60000101', 'yyyymmdd') 
WHERE TODATE in ( 
select TODATE from
(
SELECT TODATE ,row_number() over(partition by INSTRUMENT order by TODATE desc) as rn FROM schema.myTable WHERE HISTTYPE = 'Close'
) as t where t.rn=1
);