请问不支持使用开窗函数，怎么转换常规写法？

[ffw5b7]

SELECT
MAX(oc2.CREATEDTIME ) OVER (PARTITION BY oc2.RENEWAL_FIRST_CON) ,oc2.CONTRACT_ID

FROM
OSR_CONTRACT oc2

INNER join
(SELECT CONTRACT_ID FROM OSR_CONTRACT oc WHERE oc.renewal_first_con IS NULL AND IS_RENEWAL = 'N') AS a

ON a.CONTRACT_ID=oc2.RENEWAL_FIRST_CON


想查出最大时间的这条合同 id 。用时间关联查询感觉不托。一时间想不到其他写法。sql 基本功差。

[ffw5b7]

不妥……

[ffw5b7]

SELECT oc3.CONTRACT_ID from OSR_CONTRACT oc3
INNER JOIN
(
SELECT
MAX(oc2.CREATEDTIME ) AS CREATEDTIME
FROM
OSR_CONTRACT oc2
INNER join OSR_CONTRACT oc ON oc.CONTRACT_ID=oc2.RENEWAL_FIRST_CON
WHERE oc.renewal_first_con IS NULL AND oc.IS_RENEWAL = 'N'
GROUP BY oc2.RENEWAL_FIRST_CON
) AS a
ON oc3.CREATEDTIME = a.CREATEDTIME



用时间关键感觉不妥

[ffw5b7]

没有办法可能只能用时间关联先实现了。

[ffw5b7]

就是分组后取每一组内最新的数据。

[xsonglive491]

楼主可以看一下
select oc1.id
from OSR_CONTRACT as oc1
left join OSR_CONTRACT as oc2
on oc1.CONTRACT_ID=oc2.CONTRACT_ID
and oc.CREATEDTIME<oc2.CREATEDTIME
where oc2.id is null