select distinct o.MoCode,mod.SortSeq,mod.InvCode,inv.cInvName,
mod.Qty,dbo.fn_SumClum(fmbd.MoDId,2) as SMT,dbo.fn_SumClum(fmbd.MoDId,3) as DIP,dbo.fn_SumClum(fmbd.MoDId,4) as ASSY,dbo.fn_SumClum(fmbd.MoDId,5) as TEST,dbo.fn_SumClum(fmbd.MoDId,6) as PACK,
convert(varchar(100),(select MIN(CreateTime) from fc_MoRoutingBill a1
inner join fc_MoRoutingBilldetail b1
on a1.MID=b1.MID where b1.MoId=fmbd.MoId),120) as 第一报工时间,
convert(varchar(100),(select max(CreateTime) from fc_MoRoutingBill a1
inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),120) as 最后报工时间,
case when ISNULL(cast(dbo.fn_SumClum(fmbd.MoDId,2) as nvarchar),N'')!=N''
then (case when cast(mod.Qty as float)-cast(dbo.fn_SumClum(fmbd.MoDId,3) as float)=0 then '是' else '否' end) else (case when cast(mod.Qty as float)-cast
(dbo.fn_SumClum(fmbd.MoDId,6) as float)=0 then '是' else '否' end) end as 是否结案 ,
(DATEDIFF(day,(select MIN(CreateTime) from fc_MoRoutingBill a1
inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),(select max(CreateTime) from fc_MoRoutingBill a1
inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId)))-7 as 超期时间,
case when (DATEDIFF(day,(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId)))-7>0 then '已' else '未' end as 是否超期 from fc_MoRoutingBill fmb
inner join fc_MoRoutingBilldetail fmbd on fmb.Mid=fmbd.MID
inner join mom_order mo on fmbd.MoId=mo.MoId
inner join mom_orderdetail mod on fmbd.MoDId=mod.MoDId
inner join mom_morder mm on fmbd.ModId=mm.MoDId
inner join inventory inv on mod.InvCode=inv.cInvCode
查询速度极慢 请求高手 解决方案 求代码
SQL
------解决方案--------------------
执行计划发出来
------解决方案--------------------
你这个sql语句,用到distinct,又用到函数,只有写的人理解,维护的人不理解.
如果只用一个sql语句,无论什么写得再好,都谈不是优化.
我建议你把这个语句改成存储过程,中间有函数 的,改用临时表,这样结构清淅,也容易理解
------解决方案--------------------
fc_MoRoutingBill a1
inner join fc_MoRoutingBilldetail b1
on a1.MID=b1.MID
这一段SQL语句用的比较多，建议先生成临时表，再用临时表来连接。
------解决方案--------------------
(DATEDIFF(day,(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId)))-7 as 超期时间, case when (DATEDIFF(day,(select MIN(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId),(select max(CreateTime) from fc_MoRoutingBill a1 inner join fc_MoRoutingBilldetail b1 on a1.MID=b1.MID where b1.MoId=fmbd.MoId)))-7>0 then '已' else '未' end as 是否超期 from fc_MoRoutingBill fmb
为了生成列而作很多JOIN的部分可以考虑用临时表，减少表示用次数