select decc,objtype,objname,objid,usrid,timesheetid,timetypename,hour1,houradd1,flag1,aflag1,hour2,houradd2,flag2,aflag2,hour3,houradd3,flag3,aflag3,hour4,houradd4,flag4,
aflag4,hour5,houradd5,flag5,aflag5,hour6,houradd6,flag6,aflag6,hour7,houradd7,flag7,aflag7,hour0,houradd0
from (select '1' decc,
0 objtype,
'開發一部' objname,
pb.type objid,
0 usrid,
0 timesheetid,
'' timetypename,
sum(case when timesheetdetailid = 1 then wtd.manhour else 0 end) hour1,
sum(case when timesheetdetailid = 1 then wtd.manovertime else 0 end) houradd1,
0 flag1,
0 aflag1,
sum(case when timesheetdetailid = 2 then wtd.manhour else 0 end) hour2,
sum(case when timesheetdetailid = 2 then wtd.manovertime else 0 end) houradd2,
0 flag2,
0 aflag2,
sum(case when timesheetdetailid = 3 then wtd.manhour else 0 end) hour3,
sum(case when timesheetdetailid = 3 then wtd.manovertime else 0 end) houradd3,
0 flag3,
0 aflag3,
sum(case when timesheetdetailid = 4 then wtd.manhour else 0 end) hour4,
sum(case when timesheetdetailid = 4 then wtd.manovertime else 0 end) houradd4,
0 flag4,
0 aflag4,
sum(case when timesheetdetailid = 5 then wtd.manhour else 0 end) hour5,
sum(case when timesheetdetailid = 5 then wtd.manovertime else 0 end) houradd5,
0 flag5,
0 aflag5,
sum(case when timesheetdetailid = 6 then wtd.manhour else 0 end) hour6,
sum(case when timesheetdetailid = 6 then wtd.manovertime else 0 end) houradd6,
0 flag6,
0 aflag6,
sum(case when timesheetdetailid = 7 then wtd.manhour else 0 end) hour7,
sum(case when timesheetdetailid = 7 then wtd.manovertime else 0 end) houradd7,
0 flag7,
0 aflag7,
sum(wtd.manhour) hour0,
sum(wtd.manovertime) houradd0
from wk_timesheet wt
left outer join wk_timesheetdetail wtd
on wt.timesheetid = wtd.timesheetid
and (manhour > 0 or manovertime > 0)
left outer join org_userinfo ou
on wt.userid = ou.userid
left outer join pj_baseinfo pb
on wt.projectid = pb.projectid
where pb.type = 0
and (wtd.submitstate = 1 or wtd.submitstate = 2)
and wt.userid in (select u.userid from org_userinfo u left outer join dpt_departmentaccess d on u.attachdepartmentid=d.departmentid left outer join org_role a on a.roleid=d.roleid where a.roletypeid = 5 and a.principal = 1)
and wt.timestartdate = to_date('2018-01-15', 'yyyy-MM-dd')
group by pb.type
union all
select '11' decc,
0 objtype,
ou.username objname,
pb.type objid,
wt.userid usrid,
0 timesheetid,
'' timetypename,
sum(case when timesheetdetailid = 1 then wtd.manhour else 0 end) hour1,
sum(case when timesheetdetailid = 1 then wtd.manovertime else 0 end) houradd1,
0 flag1,
0 aflag1,
sum(case when timesheetdetailid = 2 then wtd.manhour else 0 end) hour2,
sum(case when timesheetdetailid = 2 then wtd.manovertime else 0 end) houradd2,
0 flag2,
0 aflag2,
sum(case when timesheetdetailid = 3 then wtd.manhour else 0 end) hour3,
sum(case when timesheetdetailid = 3 then wtd.manovertime else 0 end) houradd3,
0 flag3,
0 aflag3,
sum(case when timesheetdetailid = 4 then wtd.manhour else 0 end) hour4,
sum(case when timesheetdetailid = 4 then wtd.manovertime else 0 end) houradd4,
0 flag4,
0 aflag4,
sum(case when timesheetdetailid = 5 then wtd.manhour else 0 end) hour5,
sum(case when timesheetdetailid = 5 then wtd.manovertime else 0 end) houradd5,
0 flag5,
0 aflag5,
sum(case when timesheetdetailid = 6 then wtd.manhour else 0 end) hour6,
sum(case when timesheetdetailid = 6 then wtd.manovertime else 0 end) houradd6,
0 flag6,
0 aflag6,
sum(case when timesheetdetailid = 7 then wtd.manhour else 0 end) hour7,
sum(case when timesheetdetailid = 7 then wtd.manovertime else 0 end) houradd7,
0 flag7,
0 aflag7,
sum(wtd.manhour) hour0,
sum(wtd.manovertime) houradd0
from wk_timesheet wt
left outer join wk_timesheetdetail wtd
on wt.timesheetid = wtd.timesheetid
and (manhour > 0 or manovertime > 0)
left outer join org_userinfo ou
on wt.userid = ou.userid
left outer join pj_baseinfo pb
on wt.projectid = pb.projectid
where pb.type = 0
and (wtd.submitstate = 1 or wtd.submitstate = 2)
and wt.userid in(select u.userid from org_userinfo u left outer join dpt_departmentaccess d on u.attachdepartmentid=d.departmentid left outer join org_role a on a.roleid=d.roleid where a.roletypeid = 5 and a.principal = 1)
and wt.timestartdate = to_date('2018-01-15', 'yyyy-MM-dd')
group by pb.type, ou.username, wt.userid
union all
select '111' decc,
0 objtype,
tb.taskname objname,
pb.type objid,
wt.userid usrid,
wt.timesheetid,
cd.paraname timetypename,
sum(case when timesheetdetailid = 1 then wtd.manhour else 0 end) hour1,
sum(case when timesheetdetailid = 1 then wtd.manovertime else 0 end) houradd1,
sum(case when timesheetdetailid = 1 then wtd.submitstate else 0 end) flag1,
sum(case when timesheetdetailid = 1 then wtd.approvalstate else 0 end) aflag1,
sum(case when timesheetdetailid = 2 then wtd.manhour else 0 end) hour2,
sum(case when timesheetdetailid = 2 then wtd.manovertime else 0 end) houradd2,
sum(case when timesheetdetailid = 2 then wtd.submitstate else 0 end) flag2,
sum(case when timesheetdetailid = 2 then wtd.approvalstate else 0 end) aflag2,
sum(case when timesheetdetailid = 3 then wtd.manhour else 0 end) hour3,
sum(case when timesheetdetailid = 3 then wtd.manovertime else 0 end) houradd3,
sum(case when timesheetdetailid = 3 then wtd.submitstate else 0 end) flag3,
sum(case when timesheetdetailid = 3 then wtd.approvalstate else 0 end) aflag3,
sum(case when timesheetdetailid = 4 then wtd.manhour else 0 end) hour4,
sum(case when timesheetdetailid = 4 then wtd.manovertime else 0 end) houradd4,
sum(case when timesheetdetailid = 4 then wtd.submitstate else 0 end) flag4,
sum(case when timesheetdetailid = 4 then wtd.approvalstate else 0 end) aflag4,
sum(case when timesheetdetailid = 5 then wtd.manhour else 0 end) hour5,
sum(case when timesheetdetailid = 5 then wtd.manovertime else 0 end) houradd5,
sum(case when timesheetdetailid = 5 then wtd.submitstate else 0 end) flag5,
sum(case when timesheetdetailid = 5 then wtd.approvalstate else 0 end) aflag5,
sum(case when timesheetdetailid = 6 then wtd.manhour else 0 end) hour6,
sum(case when timesheetdetailid = 6 then wtd.manovertime else 0 end) houradd6,
sum(case when timesheetdetailid = 6 then wtd.submitstate else 0 end) flag6,
sum(case when timesheetdetailid = 6 then wtd.approvalstate else 0 end) aflag6,
sum(case when timesheetdetailid = 7 then wtd.manhour else 0 end) hour7,
sum(case when timesheetdetailid = 7 then wtd.manovertime else 0 end) houradd7,
sum(case when timesheetdetailid = 7 then wtd.submitstate else 0 end) flag7,
sum(case when timesheetdetailid = 7 then wtd.approvalstate else 0 end) aflag7,
sum(wtd.manhour) hour0,
sum(wtd.manovertime) houradd0
from wk_timesheet wt
left outer join wk_timesheetdetail wtd
on wt.timesheetid = wtd.timesheetid
and (manhour > 0 or manovertime > 0)
left outer join tk_baseinfo tb
on wt.taskid = tb.taskid
left outer join org_userinfo ou
on wt.userid = ou.userid
left outer join cfg_dictionarypara cd
on cd.paratypename = 'TK_TASKTYPE'
and cd.delflag = 0
and wt.tasktype = cd.paravalue
left outer join pj_baseinfo pb
on wt.projectid = pb.projectid
where pb.type = 0
and (wtd.submitstate = 1 or wtd.submitstate = 2)
and wt.userid in (select u.userid from org_userinfo u left outer join dpt_departmentaccess d on u.attachdepartmentid=d.departmentid left outer join org_role a on a.roleid=d.roleid where a.roletypeid = 5 and a.principal = 1)
and wt.timestartdate = to_date('2018-01-15', 'yyyy-MM-dd')
group by tb.taskname,
pb.type,
wt.userid,
wt.timesheetid,
cd.paraname,
wt.tasktype) obj
order by objid desc, usrid, timesheetid;
合計工時，wk_timesheet ,wk_timesheetdetail,tk_baseinfo這三張張表都是300萬以上的資料 ，查詢耗時30s左右，大家有沒有方法優化 一下 ，不建表磁區情況下。
uj5u.com熱心網友回復：
這么長的sql....做個10046事件，分析耗時主要在哪，然后針對性分析吧。
uj5u.com熱心網友回復：
1、三個SQL用UNION ALL合并起來，每個SQL分別優化，檢查性能；2、外關聯要去掉，沒有用處；
3、wk_timesheet表上要有索引，timestartdate列應該是索引的第一列；
uj5u.com熱心網友回復：
1、三個SQL用UNION ALL合并起來，每個SQL分別優化，檢查性能；2、外關聯要去掉，沒有用處；
3、wk_timesheet表上要有索引，timestartdate列應該是索引的第一列；
第1，3都弄了，第2條外關聯要去掉是外關聯改成行內接？
uj5u.com熱心網友回復：
從SQL寫法上看，大查詢（三個UNION ALL合并的SQL，不是子查詢）有外關聯和沒有外關聯的查詢結果是相同的，因此不需要外關聯，你也可以驗證下。uj5u.com熱心網友回復：
這和SQL的寫法關系不大，建不建磁區關系也不大，最有效的辦法 就是用建一個聚簇，用三個表聚簇吧轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/68261.html
標籤：高級技術