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

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 基本功差。

oc2

select

写法

from

5 条回复 • 2023-02-08 16:15:40 +08:00

ffw5b7

2023-02-06 16:04:14 +08:00 via Android

不妥……

ffw5b7

2023-02-06 16:08:44 +08:00 via Android

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

2023-02-07 09:04:13 +08:00 via Android

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

ffw5b7

2023-02-07 10:16:28 +08:00 via Android

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

xsonglive491

2023-02-08 16:15:40 +08:00

楼主可以看一下
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