17,377
社区成员
发帖
与我相关
我的任务
分享
select aa.workitem_id,aa.formset_inst_id,bb.receive_time,aa.complete_time from
(select a.workitem_id,a.formset_inst_id,a.complete_time from mv_workitem a where workitem_id in (
select max(t.workitem_id) from mv_workitem t where t.formset_id=347 and t.receiver_dept='总裁办公室(股份)' and t.app_type_name='部门阅办文件' group by t.formset_inst_id) order by workitem_id)aa,
(select a.workitem_id,a.formset_inst_id,a.receive_time from mv_workitem a where a.receive_time in(
select min(t.receive_time)
from mv_workitem t where t.formset_id=347 and t.receiver_dept='总裁办公室(股份)' and t.app_type_name='部门阅办文件' group by t.formset_inst_id))bb where aa.workitem_id=bb.workitem_id
;WITH cte AS (select max(t.workitem_id) maxWid, min(t.receive_time) minRtime
from mv_workitem t
where t.formset_id = 347
and t.receiver_dept = '总裁办公室(股份)'
and t.app_type_name = '部门阅办文件'
group by t.formset_inst_id
)
SELECT a.workitem_id, a.formset_inst_id, a.complete_time
FROM mv_workitem a,cte c
WHERE workitem_id= c.maxWid AND receive_time =c.minRtime
ORDER BY workitem_id
SELECT aa.workitem_id,
aa.formset_inst_id,
bb.receive_time,
aa.complete_time
FROM ( SELECT a.workitem_id, a.formset_inst_id, a.complete_time
FROM mv_workitem a
WHERE workitem_id IN
( SELECT MAX (t.workitem_id)
FROM mv_workitem t
WHERE t.formset_id = 347
AND t.receiver_dept = '总裁办公室(股份)'
AND t.app_type_name = '部门阅办文件'
)
) aa,
(SELECT a.workitem_id, a.formset_inst_id, a.receive_time
FROM mv_workitem a
WHERE a.receive_time IN
( SELECT MIN (t.receive_time)
FROM mv_workitem t
WHERE t.formset_id = 347
AND t.receiver_dept = '总裁办公室(股份)'
AND t.app_type_name = '部门阅办文件'
)) bb
WHERE aa.workitem_id = bb.workitem_id
--第二句最后修改下
GROUP BY FORMSET_INST_ID;
--从你的sql分析,
-- 先找到最大的 WORKITEM_ID 对应的信息
-- 再找到最小的 RECEIVE_TIME 对应的信息
-- 然后两组信息通过 WORKITEM_ID 再关联
-- 实际上相当于同时找 WORKITEM_ID 最大 和 RECEIVE_TIME 最小的信息,因此改造如下
SELECT WORKITEM_ID, FORMSET_INST_ID, COMPLETE_TIME, RECEIVE_TIME
FROM MV_WORKITEM A
WHERE (WORKITEM_ID, RECEIVE_TIME) IN
(SELECT MAX(T.WORKITEM_ID), MIN(T.RECEIVE_TIME)
FROM MV_WORKITEM T
WHERE T.FORMSET_ID = 347
AND T.RECEIVER_DEPT = '总裁办公室(股份)'
AND T.APP_TYPE_NAME = '部门阅办文件'
GROUP BY T.FORMSET_INST_ID);
--另外如果从业务逻辑看(猜测)
--猜测你想找的数据是,对于每一个 FORMSET_INST_ID ,有接收时间(RECEIVE_TIME)
----有不同的处理环节(WORKITEM_ID)
----每一个处理环节有完成时间(COMPLETE_TIME)
----所以猜测你想找的数据是
------对每一个FORMSET_INST_ID,最早收到的,最大处理环节 的信息
--测试下下面的语句是否满足你的要求吧
SELECT FORMSET_INST_ID,
MAX(COMPLETE_TIME)KEEP(DENSE_RANK LAST ORDER BY WORKITEM_ID)COMPLETE_TIME
MAX(WORKITEM_ID)KEEP(DENSE_RANK FIRST ORDER BY RECEIVE_TIME)WORKITEM_ID,
MIN(RECEIVE_TIME)KEEP(DENSE_RANK FIRST ORDER BY RECEIVE_TIME)RECEIVE_TIME
FROM MV_WORKITEM A
WHERE T.FORMSET_ID = 347
AND T.RECEIVER_DEPT = '总裁办公室(股份)'
AND T.APP_TYPE_NAME = '部门阅办文件'
GROUP BY FORMSET_INST_ID,COMPLETE_TIME;