FROM bwwf_Tracking_Workflows as tw LEFT JOIN bwwf_Workflows as w on tw.workflowId=w.workflowId where State=2 and w.Enabled=1 and w.Iscurrent=1 Group by tw.workflowId---改成这样就可以了,单独看你的应该还可以简化 FROM bwwf_Tracking_Workflows tw LEFT JOIN bwwf_Workflows w on tw.workflowId=w.workflowId where State=2 and w.Enabled=1 and w.Iscurrent=1 Group by tw.workflowId
---改了下 ,照你的意思来的 不过我觉得应该还有简化的 不明白业务SELECT max(w.WorkFlowName) as StatName, (case when sum((FinishedTime-StartedTime)*24*60)/count(*)>1439 then trunc(sum((FinishedTime-StartedTime)*24*60)/(count(*)*1440))||'天'|| (case when mod(sum((FinishedTime-StartedTime)*24*60)/count(*),1440)/60=0 then '' else mod(sum((FinishedTime-StartedTime)*24*60)/count(*),1440)/60||'小时' end) else (case when sum((FinishedTime-StartedTime)*24*60)/count(*)>=60 then sum((FinishedTime-StartedTime)*24*60)/count(*)/60 ||'小时'|| (case when mod(sum((FinishedTime-StartedTime)*24*60)/count(*),60) = 0 then '' else mod(sum((FinishedTime-StartedTime)*24*60)/count(*),60)||'分钟' end) else (case when sum((FinishedTime-StartedTime)*24*60*60)/count(*)<60 then '' else (sum((FinishedTime-StartedTime)*24*60*60)/count(*)/60) ||'分钟' end)|| (case when mod(sum((FinishedTime-StartedTime)*24*60*60)/count(*),60) = 0 then '' else mod(sum((FinishedTime-StartedTime)*24*60*60)/count(*),60)||'秒' end) end) end) as StatInstance,tw.WorkflowId as StatID FROM bwwf_Tracking_Workflows tw LEFT JOIN bwwf_Workflows w on tw.workflowId=w.workflowId where State=2 and w.Enabled=1 and w.Iscurrent=1 Group by tw.workflowId
FROM bwwf_Tracking_Workflows as tw
LEFT JOIN bwwf_Workflows as w on tw.workflowId=w.workflowId
where State=2 and w.Enabled=1 and w.Iscurrent=1
Group by tw.workflowId---改成这样就可以了,单独看你的应该还可以简化
FROM bwwf_Tracking_Workflows tw
LEFT JOIN bwwf_Workflows w on tw.workflowId=w.workflowId
where State=2 and w.Enabled=1 and w.Iscurrent=1
Group by tw.workflowId
(case
when sum((FinishedTime-StartedTime)*24*60)/count(*)>1439
then trunc(sum((FinishedTime-StartedTime)*24*60)/(count(*)*1440))||'天'||
(case when mod(sum((FinishedTime-StartedTime)*24*60)/count(*),1440)/60=0 then ''
else mod(sum((FinishedTime-StartedTime)*24*60)/count(*),1440)/60||'小时' end)
else
(case when sum((FinishedTime-StartedTime)*24*60)/count(*)>=60
then sum((FinishedTime-StartedTime)*24*60)/count(*)/60 ||'小时'||
(case when mod(sum((FinishedTime-StartedTime)*24*60)/count(*),60) = 0 then ''
else mod(sum((FinishedTime-StartedTime)*24*60)/count(*),60)||'分钟' end)
else
(case when sum((FinishedTime-StartedTime)*24*60*60)/count(*)<60 then ''
else (sum((FinishedTime-StartedTime)*24*60*60)/count(*)/60) ||'分钟' end)||
(case when mod(sum((FinishedTime-StartedTime)*24*60*60)/count(*),60) = 0 then ''
else mod(sum((FinishedTime-StartedTime)*24*60*60)/count(*),60)||'秒' end)
end)
end)
as StatInstance,tw.WorkflowId as StatID
FROM bwwf_Tracking_Workflows tw
LEFT JOIN bwwf_Workflows w on tw.workflowId=w.workflowId
where State=2 and w.Enabled=1 and w.Iscurrent=1
Group by tw.workflowId