mysql/oracle LEFT JOIN 取时间最大的数据-拓冰企业网站定制

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

        select t.task_id,t.task_name, maxr.task_result_idfrom 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 (
selecta.task_id,a.task_result
from(selectrow_number() over(partition by task_id order by execution_time desc) as irank,t.*fromdg_dq_task_rule_result t ) a
wherea.irank = 1
) b
on 
tsk.task_id = b.task_id

oracle update语句

UPDATE CUST_PROBLEM_EXTERNAL tsk
SET tsk.F_PROGRESS = (SELECT a.F_PROGRESSFROM (-- 先获取子表所有记录的最新排序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 irankFROM CUST_PROBLEM_EXTERNAL_PROGRESS t) a-- 在这里与主表关联WHERE a.F_FOREIGN_ID = tsk.F_IDAND a.irank = 1
)
WHERE tsk.F_ID = '738729951393514693';

oracle mysql兼容SQL

SELECTsu.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,(SELECTDISTINCT mbmcFROMMD_CZ_MB_ZB_LOCAL mcmzWHEREmcmz.MBBM = su.USER_CLASSIFICATIONAND mcmz.FLAG = 1AND 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
FROMsys_user_group_user sugu
INNER JOIN sys_user su ONsugu.USER_ID = su.USER_IDAND su.DEL_FLAG = '0'AND su.ON_THE_JOB_STATUS = '1'AND su.STATUS IN ('0', '1')
LEFT JOIN sys_dept_xl sdx ONsu.DEPT_ID = sdx.DEPT_ID
LEFT JOIN sys_dept_xl sdx2 ONsu.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
WHEREsugu.USER_GROUP_ID = 1784460521241276417
ORDER BYsu.BRANCH_DEPT_ID,su.DEPT_ID

资讯详情

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