sql语句优化,求大神来指点一下

select * from (select max(j1.form_no) as 施工合同编号,max(to_char(j1.created_date,'yyyy-mm-dd')) as 施工合同日期,
max(j.form_no) as 设计合同编号,max(b.name) as 客户名称,max(a.community_name) as 小区,
max(a.room_no) as 楼栋号,max(k7.item_value) as 工程总产值,max(k.item_value) as 优惠,
max(k8.item_value) as 优惠后总产值,max(e.user_name) as 业务员,max(a.recommender) as 推荐人,max(h.user_name) as 前台,
max(h1.user_name) as 设计师 ,max(k.creator_name) as 预算员,max(k1.item_value) as 直接费
,max(k2.item_value) as 绘图费预算,max(k3.item_value) as 设计费,max(k4.item_value) as 工程首付款,
max(k5.item_value) as 主材预算款款,max(k9.item_value) as 累计实收工程款,max(m.total_amount) as 施工定金,max(m1.total_amount) as 设计定金,
max(k6.item_value) as 开工日期预计 ,case when now()-to_date(max(k6.item_value), 'yyyy-mm-dd') > interval '30 day' then '是'
else '否' end as 是否超期,max(a.status_name) as 订单状态,a.id
from t_dcr_order a left join t_customer b on a.customer_id = b.id left join
t_service_team c on a.id = c.ref_dcro_id right join t_role d on d.id = c.role_id
and d.role_code = 'ywy' -- 业务员
left join t_user e on e.id = c.user_id left join t_service_team f on
a.id = f.ref_dcro_id right join t_role g on g.id = f.role_id
and g.role_code = 'qtzg' --前台主管
left join t_user h on h.id = f.user_id left join t_service_team f1 on
a.id = f1.ref_dcro_id right join t_role g1 on g1.id = f1.role_id
and g1.role_code = 'sjs' --设计师
left join t_user h1 on h1.id = f1.user_id left join t_milestone i on
i.ref_dcro_id = a.id and i.ms_code = '0040' --签设计合同
right join t_milestone i1 on
i1.ref_dcro_id = a.id and i1.ms_code = '0080' and i1.is_complete = 'Y' --签施工合同
left join t_doc_milestone_event j on a.id = j.ref_dcro_id
and j.ms_type = '2' --设计合同文档
left join t_doc_milestone_event j1 on a.id = j1.ref_dcro_id
and j1.ms_type = '4' --施工合同文档
left join t_dcr_proj_budget k on a.id = k.ref_dcro_id right join t_budget_item l
on k.ref_item_id = l.id and l.item_code = 'youHui_yu' --优惠预计
left join t_dcr_proj_budget k1 on a.id = k1.ref_dcro_id right join t_budget_item l1
on k1.ref_item_id = l1.id and l1.item_code = 'zhiJieFei_yu' --直接费预算
left join t_dcr_proj_budget k2 on a.id = k2.ref_dcro_id right join t_budget_item l2
on k2.ref_item_id = l2.id and l2.item_code = 'huiTuFei_yu' --绘图费预算
left join t_dcr_proj_budget k3 on a.id = k3.ref_dcro_id right join t_budget_item l3
on k3.ref_item_id = l3.id and l3.item_code = 'sheJiFei_yu' --设计费预算
left join t_dcr_proj_budget k4 on a.id = k4.ref_dcro_id right join t_budget_item l4
on k4.ref_item_id = l4.id and l4.item_code = 'firstFee_ying' --首付款应收
left join t_dcr_proj_budget k5 on a.id = k5.ref_dcro_id right join t_budget_item l5
on k5.ref_item_id = l5.id and l5.item_code = 'zhuCaiKuai_yu' --主材款预算
left join t_dcr_proj_budget k6 on a.id = k6.ref_dcro_id right join t_budget_item l6
on k6.ref_item_id = l6.id and l6.item_code = 'kaiGongDate_yu' --开工日期预计
left join t_dcr_proj_budget k7 on a.id = k7.ref_dcro_id right join t_budget_item l7
on k7.ref_item_id = l7.id and l7.item_code = 'zongChanZhi_yu' --工程总产值
left join t_dcr_proj_budget k8 on a.id = k8.ref_dcro_id right join t_budget_item l8
on k8.ref_item_id = l8.id and l8.item_code = 'huiHouChanZhi_yu' --优惠后总产值预算
left join t_dcr_proj_budget k9 on a.id = k9.ref_dcro_id right join t_budget_item l9
on k9.ref_item_id = l9.id and l9.item_code = 'receiveFeeAll' --累积实收工程款
left join t_dcro_receipt m on m.ref_dcro_id = a.id
and m.receipt_type = 'ConstructionOfTheDeposit' --施工定金
left join t_dcro_receipt m1 on m1.ref_dcro_id = a.id
and m1.receipt_type = 'DesignTheDeposit' --设计定金
where a.owner_company_id =16737 group by a.id ) t where 1=1

4个回答

我去。。。 这sql也太长了点吧

你这个是个巨坑,把max放到关联表中
left join (select max(b.name) as 客户名称 t_customer group by ** )b on a.customer_id = b.id

表关联的太多了,可以把SQL落地成两个表,再join

这个sql就是一个巨坑,估计你这执行速度,慢的一逼吧!

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问