如何从同一表的不同列中获取最大日期

Question

需要帮助吗?我有以下表格:

Lastdate24/05/202125/06/202029/10/202112/05/201808/11/201930/12/2020

您可以使用窗口函数:

select Accountno, TrackNo, PercentInc, CreateDate, max_Lastdate
from (select t.*,
row_number() over (partition by accountno, trackno order by createdate desc) as seqnum,
max(lastdate) over (partition by accountno, trackno) as max_lastdate
from t
) t
where seqnum = 1;

有很多方法可以用SQL给猫涂皮。我觉得很直观的一种方法是:

从这样的查询中获取每个TrackNo的最长日期:

SELECT TrackNo, MAX(CreateDate) MaxCreateDate, MAX(Lastdate) MaxLastDate 
FROM Table 
GROUP BY TrackNo

然后用子查询和两个内部连接将它链接到你想要的两条记录:

SELECT maxLastDate.Accountno, maxCreateDate.TrackNo, maxCreateDate.PercentInc, maxCreateDate.CreateDate, maxLastDate.Lastdate
FROM (
SELECT TrackNo, MAX(CreateDate) MaxCreateDate, MAX(Lastdate) MaxLastDate 
FROM Table 
GROUP BY TrackNo
) AS maxDates
INNER JOIN Table AS maxCreateDate 
ON maxCreateDate.TrackNo = maxDates.TrackNo AND maxCreateDate.CreateDate = maxDates.MaxCreateDate
INNER JOIN Table AS maxLastDate 
ON maxLastDate.TrackNo = maxDates.TrackNo AND maxLastDate.Lastdate = maxDates.MaxLastDate