求大神优化下sql 查询，数据才1000条， 结果用了40秒
0
悬赏园豆：10
[已解决问题]
解决于 2015-10-27 16:50
with TT1 AS (
SELECT 编号1 , 编号2 , 编号3, 产出, 取样,接头 , 时间, 任务单号, 胶水号, 品名 , 卷首 , 卷尾, 品名2, 厂家2, 批号2, 幅宽2
, sum(取样+接头+卷首+卷尾) as 耗损, sum(取样+接头+卷首+卷尾+产出) as 投入, convert(varchar,convert (decimal(10,2),(产出/(sum(取样+接头+卷首+卷尾+产出))*100)))+'%' as 良率
from (
SELECT 编号1 , 编号2 , 编号3, 产出, 取样,接头 , 时间, 任务单号, 胶水号,x.ProductName as 品名 ,Discovers as 卷首 ,Capuchin as 卷尾,Type as 品名2,Supplier as 厂家2,Number as 批号2,Width as 幅宽2
from (
(select [PVNo] as 编号1 ,[UWindoneNo] as 编号2 ,[UWindtwoNo] as 编号3,[OutputGoodProduct] as 产出,[Sampling] as 取样,
[Joint] as 接头 ,[Entrytime] as 时间,[ListNo] as 任务单号,[AdhesiveNO] as 胶水号
FROM [PVMS].[dbo].[Pro_Winding] where UWindoneNo like 'WD%' ) as a
LEFT JOIN Mkt_ProductionOrder_Tasklist x ON a.任务单号=x.TasklistNo LEFT JOIN Pro_Unwinding c ON c.PVNo=a.编号2
LEFT JOIN Pro_Corona d ON d.PVNO=a.编号3 LEFT JOIN Pro_MaterialData e ON d.MaterNo=e.PVNO ) ) as t
where 时间 between '2015-1-11 14:28:25.767' and '2015-10-22 14:28:25.767' -- 编号1='WD1510220057' and 添加条件
group by 编号1,编号2 , 编号3, 产出, 取样,接头 , 时间, 任务单号, 胶水号, 品名 , 卷首 , 卷尾, 品名2, 厂家2, 批号2, 幅宽2
) ,
TT2 AS (select aa.PVNo,UWindoneNo,UWindtwoNo ,Type as 品名1,Supplier as 厂家1,Number as 批号1,Width as 幅宽1
from (select PVNo,UWindoneNo,UWindtwoNo from Pro_Winding where UWindoneNo like 'UW%') as aa
LEFT JOIN Pro_Unwinding bb ON aa.UWindoneNo=bb.PVNO LEFT JOIN Pro_MaterialData cc ON bb.MaterNo=cc.PVNO
),
TT3 AS (select aa.PVNo,UWindoneNo,UWindtwoNo ,Type as 品名3,Supplier as 厂家3,Number as 批号3,Width as 幅宽3
from (select PVNo,UWindoneNo,UWindtwoNo from Pro_Winding where UWindoneNo like 'UW%') as aa
LEFT JOIN Pro_Unwinding bb ON aa.UWindoneNo=bb.PVNO LEFT JOIN Pro_MaterialData cc ON aa.UWindtwoNo=cc.PVNO
)
SELECT 编号1 , 编号2 , 编号3, 产出, 取样,接头 , 时间, 任务单号, 胶水号, 品名 , 卷首 , 卷尾, 品名2, 厂家2, 批号2, 幅宽2
, 耗损, 投入, 良率,TT2.UWindoneNo as 编号A,TT2.UWindtwoNo as 编号4,品名1 , 厂家1, 批号1, 幅宽1, 品名3, 厂家3, 批号3, 幅宽3
from TT1,TT2,TT3 where TT1.编号2=TT2.PVNo and TT1.编号2=TT3.PVNo
最佳答案
0
最终解决了，原来主表自身直接需要先关联查询出编号A和编号4， 后期直接做左连接都可以完成。
SELECT 任务单号, 品名 , 品名1, 批号1, 幅宽1,厂家1, 品名3,批号3, 幅宽3 ,厂家3, 品名2,批号2, 幅宽2,厂家2, 时间,编号2 , 编号1 , 编号3,
sum(取样+接头+卷首+卷尾+产出) as 投入, 产出,sum(取样+接头+卷首+卷尾) as 耗损,
convert(varchar,convert (decimal(10,2),(产出/(sum(取样+接头+卷首+卷尾+产出))*100)))+'%' as 良率 ,
卷首 , 卷尾,取样,接头 , 胶水号 ,编号A,编号4
from (
SELECT 编号1 , 编号2 , 编号3, 编号A,编号4,产出, 取样,接头 , 时间,
任务单号, 胶水号,x.ProductName as 品名 ,Discovers as 卷首 ,Capuchin as 卷尾,ts.Type as 品名2,
ts.Supplier as 厂家2,ts.Number as 批号2,ts.Width as 幅宽2,
ee.Type as 品名1,ee.Supplier as 厂家1,ee.Number as 批号1,ee.Width as 幅宽1,
e.Type as 品名3,e.Supplier as 厂家3,e.Number as 批号3,e.Width as 幅宽3
from (
(select w.[PVNo] as 编号1 ,w.[UWindoneNo] as 编号2 ,w.[UWindtwoNo] as 编号3,w.[OutputGoodProduct] as 产出,w.[Sampling] as 取样,
w.[Joint] as 接头 ,w.[Entrytime] as 时间,w.[ListNo] as 任务单号,w.[AdhesiveNO] as 胶水号 ,q.UWindoneNo AS 编号A,q.UWindtwoNo as 编号4
FROM [PVMS].[dbo].[Pro_Winding] w ,[PVMS].[dbo].[Pro_Winding] q where W.UWindoneNo like 'WD%' AND w.UWindoneNo=q.PVNo
) as a
LEFT JOIN Pro_Unwinding c ON c.PVNo=a.编号A LEFT JOIN Pro_MaterialData ee ON c.MaterNo=ee.PVNO --涂布基材（品1）
LEFT JOIN Pro_Corona dd ON dd.PVNO=a.编号3 LEFT JOIN Pro_MaterialData ts ON dd.MaterNo=ts.PVNO --第二面基材(品2）
LEFT JOIN Pro_Corona d ON d.PVNO=a.编号4 LEFT JOIN Pro_MaterialData e ON d.MaterNo=e.PVNO --第一面基材（品3）
LEFT JOIN Mkt_ProductionOrder_Tasklist x ON a.任务单号=x.TasklistNo ) ) as t
where 1=1
--时间 between '2015-1-11 14:28:25.767' and '2015-10-22 14:28:25.767' --
--and 编号1='WD1510220057' --and 添加条件
group by 编号1,编号2 , 编号3, 产出, 取样,接头 , 时间, 任务单号, 胶水号, 品名 , 卷首 , 卷尾, 品名2, 厂家2, 批号2, 幅宽2,
品名1, 厂家1, 批号1, 幅宽1, 品名3, 厂家3, 批号3, 幅宽3 ,编号A,编号4
结果只要1秒就结束了。
其他回答(7)
0
先看看单个查询花费的时间，哪个最多，就优化哪个
收获园豆：1
0
眼花缭乱 ~
收获园豆：1
0
用Inner join 和Left join可以实现你要的东西. 具体看你上一个问题.
收获园豆：2
0
SQL写成这样，假以时日必成SQL大神。
别的不说，同情一下下一个入坑的程序员。
收获园豆：2
哈哈。。。
强烈赞同大叔意见，我等sql弱者已经潜意识都逃避该sql
0
眼花了
收获园豆：1
0
你这个完全掉坑里了
收获园豆：1
0
1000条 直接全部读取, c#处理,小数据量可以这样处理
收获园豆：2
