首页新闻找找看学习计划

求多表查询或者存储过程方式是否容易实现

0
悬赏园豆:20 [待解决问题]

目前有5个表, 进行追溯查询问题,现在知道逻辑关系, 能否通过sql写出查询或者存储过程方式查询呢 ? 查询条件为 时间或者任务单号, 从上到下,以第一个查询的第八条为例子 。最后excel是一条结果

问题补充:

现在写出的语句为:

 

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

数据现在都显示出来, 但是数据查询很快,一个9000数据用来40秒, 但是独立查询 TT1,TT2,TT3, 分别为6秒,1秒,1 秒

求大神优化

rocky54321的主页 rocky54321 | 初学一级 | 园豆:133
提问于:2015-10-22 18:02
< >
分享
所有回答(3)
0

json

Kerwin1202 | 园豆:68 (初学一级) | 2015-10-22 18:18
0
SELECT 
    win.PVNO AS [编号1],
    win.[UWindoneNo] AS [编号2],
    win.[OutputGoodProduct] AS [产出], 
    win.[Sampling] AS [取样],
    win.[Joint] AS [接头],
    win.[Entrytime] AS [时间],
    win.[ListNo] AS 任务单号,
    win.[AdhesiveNo] AS [胶水号]
    unWin.[Discovers] AS [卷首],
    unWin.[Capuchin] AS [卷尾],
    (win.Sampling+win.Joint+unWin.Discovers+unWin.Capuchin) AS [损耗],
    (win.OutputGoodProduct+win.Sampling+win.Joint+unWin.Discovers+unWin.Capuchin) AS [投入]
    (win.OutputGoodProduct/(win.OutputGoodProduct+win.Sampling+win.Joint+unWin.Discovers+unWin.Capuchin)) AS [良率],
    ma1.[Type] AS [品名1],
    ma1.[Supplier] AS [厂家1],
    ma1.[Number] AS [批号1],
    ma1.[Width] AS [幅宽1],
    ma2.[Type] AS [品名2],
    ma2.[Supplier] AS [厂家2],
    ma2.[Number] AS [批号2],
    ma2.[Width] AS [幅宽2],
    ma3.[Type] AS [品名3],
    ma3.[Supplier] AS [厂家3],
    ma3.[Number] AS [批号3],
    ma3.[Width] AS [幅宽3],
FROM PVMS.dbo.Pro_Winding win
INNER JOIN [PVMS].[dbo].[Pro_Unwinding] unWin ON win.PVNO=unwin.PVNO
LEFT JOIN [PVMS].[dbo].[Pro_MaterialData] ma1 ON ma1.PVNO=unWin.MaterNo
LEFT JOIN [PVMS].[dbo].[Pro_Corona] co ON co.PVNO=unWin.UWindtwoNo
LEFT JOIN [PVMS].[dbo].[Pro_MaterialData] ma2 ON ma2.PVNO=co.MaterNo
LEFT JOIN [PVMS].[dbo].[Pro_MaterialData] ma3 ON ma3.PVNO=unWin.UWindtwoNo
WHERE win.UWindoneNo like 'WD%'

品名的数据你没给.  所以还少一个品名.  条件根据你自己需要加.  大体是这样.  可能有点bug. 需要你自己调一下. 毕竟我没有数据..

李丶GuanYao | 园豆:1228 (小虾三级) | 2015-10-23 10:18
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

rocky54321 | 园豆:133 (初学一级) | 2015-10-27 14:37
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册