mysql/oracle LEFT JOIN 取时间最大的数据

如下SQL ，一次任务执行结果 没有聚合到任务结果表，可以在任务结果表中查询出这个任务结果id，然后可根据任务结果id内存遍历或者通过not exists进行结果明细的成功失败查询。

        select t.task_id,t.task_name, maxr.task_result_id
        from dg_dq_task t 
        <if test="taskResult != null">
            left join (select a.task_id, a.task_result_id from (select row_number() over(partition by task_id order by execution_time desc) as irank, t.*
            from dg_dq_task_rule_result t ) a where a.irank = 1) b on tsk.task_id = b.task_id
        </if>

            <if test="taskResult != null">
                and exists (select 1 from dg_dq_task_rule_result trr where b.task_result_id = trr.task_result_id)
                <choose>
                    <when test="taskResult == '1'.toString()">
                        and not exists (select 1 from dg_dq_task_rule_result trr where b.task_result_id = trr.task_result_id and trr.task_result in (0, 2, 3, 4))
                    </when>
                    <when test="taskResult == '2'.toString()">
                        and not exists (select 1 from dg_dq_task_rule_result trr where b.task_result_id = trr.task_result_id and trr.task_result in (0, 1, 3, 4))
                    </when>
                    <!-- 其他情况，非成功和失败 -->
                    <when test="taskResult == '9'.toString()">
                        and not exists (select 1 from dg_dq_task_rule_result trr where b.task_result_id = trr.task_result_id and trr.task_result in (1, 2))
                    </when>
                </choose>
            </if>
        

如下SQL 新语法可获取分组后 最后执行（order by）的数据。

select tsk.*, b.task_result from dg_dq_task tsk left join (
select
        a.task_id,
        a.task_result
from
        (
        select
                row_number() over(partition by task_id order by execution_time desc) as irank,
                t.*
        from
                dg_dq_task_rule_result t ) a
where
        a.irank = 1
) b
on 
tsk.task_id = b.task_id

oracle update语句

UPDATE CUST_PROBLEM_EXTERNAL tsk
SET tsk.F_PROGRESS = (
    SELECT a.F_PROGRESS
    FROM (
        -- 先获取子表所有记录的最新排序
        SELECT 
            t.F_PROGRESS,
            t.F_FOREIGN_ID,
            row_number() over(partition by t.F_FOREIGN_ID order by t.S_CREATE_TIME desc) as irank
        FROM CUST_PROBLEM_EXTERNAL_PROGRESS t
    ) a
    -- 在这里与主表关联
    WHERE a.F_FOREIGN_ID = tsk.F_ID
      AND a.irank = 1
)
WHERE tsk.F_ID = '738729951393514693'; 

oracle mysql兼容SQL

SELECT
	su.USER_ID,
	su.USER_NAME,
	su.NICK_NAME,
	su.BRANCH_DEPT_ID,
	sdx2.DEPT_NAME AS BRANCH_DEPT_NAME,
	su.DEPT_ID,
	sdx.DEPT_NAME,
	sugu.USER_GROUP_TYPE,
	(
	SELECT
		DISTINCT mbmc
	FROM
		MD_CZ_MB_ZB_LOCAL mcmz
	WHERE
		mcmz.MBBM = su.USER_CLASSIFICATION
		AND mcmz.FLAG = 1
		AND mcmz.ZT = 1) USER_CLASSIFICATION,
--	(
--	SELECT
--		DISTINCT mbmc
--	FROM
--		MD_CZ_MB_ZB_LOCAL mcmz
--	WHERE
--		mcmz.MBBM = su.TRADE
--		AND mcmz.FLAG = 1
--		AND mcmz.ZT = 1) TRADE,
--	(SELECT
--		rn.mbmc
--	FROM
--	(SELECT mcmz.mbmc ROW_NUMBER() OVER (PARTITION BY mcmz.MBBM ORDER BY CZSJ desc) AS rn
--	FROM 
--	MD_CZ_MB_ZB_LOCAL mcmz
--	WHERE
--		mcmz.MBBM = su.TRADE
--		AND mcmz.FLAG = 1
--		AND mcmz.ZT = 1) rn=1) TRADE,

   mcmz.mbmc AS TRADE,
   
   upost.mbmc AS POST_NAME
--	(
--	SELECT
--		DISTINCT mbmc
--	FROM
--		MD_CZ_MB_ZB_LOCAL mcmz
--	WHERE
--		mcmz.MBBM = su.POST_NAME
--		AND mcmz.FLAG = 1
--		AND mcmz.ZT = 1) POST_NAME
FROM
	sys_user_group_user sugu
INNER JOIN sys_user su ON
	sugu.USER_ID = su.USER_ID
	AND su.DEL_FLAG = '0'
	AND su.ON_THE_JOB_STATUS = '1'
	AND su.STATUS IN ('0', '1')
LEFT JOIN sys_dept_xl sdx ON
	su.DEPT_ID = sdx.DEPT_ID
LEFT JOIN sys_dept_xl sdx2 ON
	su.BRANCH_DEPT_ID = sdx2.DEPT_ID
left join (select * from (select b.*, (row_number() over(partition by b.MBBM order by b.CZSJ desc)) rn from MD_CZ_MB_ZB_LOCAL b) e WHERE e.ZT=1 AND e.FLAG = 1 and rn = 1) mcmz on su.TRADE= mcmz.MBBM
left join (select * from (select b.*, (row_number() over(partition by b.MBBM order by b.CZSJ desc)) rn from MD_CZ_MB_ZB_LOCAL b) e WHERE e.ZT=1 AND e.FLAG = 1 and rn = 1) upost on su.POST_NAME= upost.MBBM
WHERE
	sugu.USER_GROUP_ID = 1784460521241276417
ORDER BY
	su.BRANCH_DEPT_ID,
	su.DEPT_ID