select b.*, a.currentworkitemname as huanjie, t.participantcn as shenheren
from (select mt.task_id,
mt.task_type as cust_no,
mt.task_name cust_name,
p.packageid liuchengno,
to_char(mt.create_time, 'yyyy-MM-dd') as apply_date,
pe.name as shenqingren,
d.name as jingbanhang,
max(t.workitemid) as workitemid, -- 获得最大的那项
p.name as leixing,
mt.task_status as status,
t.processInstanceId
from WF_ACTIVITYINFO t,
WF_PACKAGE p,
b_ma_markting_task mt,
ec_org_person pe,
ec_org_department d
where t.processdefinitionid = 'wf_ocrm_marketingtask_vv_1'
and t.businessobjectid = mt.task_id
and p.packageid = t.processdefinitionid
and mt.create_man = pe.manager_no
and pe.orgid = d.id
and mt.create_man = 'MG0000000000'
group by mt.task_id,
-- 使用group by进行分组
mt.task_type,
mt.task_name,
p.packageid,
mt.create_time,
pe.name,
d.name,
p.name,
mt.task_status,
t.processInstanceId) b,
wf_processinstance_ext a,
WF_ACTIVITYINFO t
where b.processInstanceId = a.processInstanceId
and t.workitemid = b.workitemid
order by apply_date desc;
from (select mt.task_id,
mt.task_type as cust_no,
mt.task_name cust_name,
p.packageid liuchengno,
to_char(mt.create_time, 'yyyy-MM-dd') as apply_date,
pe.name as shenqingren,
d.name as jingbanhang,
max(t.workitemid) as workitemid, -- 获得最大的那项
p.name as leixing,
mt.task_status as status,
t.processInstanceId
from WF_ACTIVITYINFO t,
WF_PACKAGE p,
b_ma_markting_task mt,
ec_org_person pe,
ec_org_department d
where t.processdefinitionid = 'wf_ocrm_marketingtask_vv_1'
and t.businessobjectid = mt.task_id
and p.packageid = t.processdefinitionid
and mt.create_man = pe.manager_no
and pe.orgid = d.id
and mt.create_man = 'MG0000000000'
group by mt.task_id,
-- 使用group by进行分组
mt.task_type,
mt.task_name,
p.packageid,
mt.create_time,
pe.name,
d.name,
p.name,
mt.task_status,
t.processInstanceId) b,
wf_processinstance_ext a,
WF_ACTIVITYINFO t
where b.processInstanceId = a.processInstanceId
and t.workitemid = b.workitemid
order by apply_date desc;
-- 获得每个分组workitemid最大的那项
select *
from (select a.*,
row_number() over(partition by a.cust_no order by a.refer_date) rn
from b_oc_rank_verify a)
where rn = 1