存储过程，游标

qq_40222134 2017-09-20 10:36:47

有俩个查询出来的表
表一

表二

然后我想把表二里面的数据加到表一中去
就像这样

这个是我自己做的，但是那些值不对，所以想问问要怎样做

表一的查询语句
SELECT mad_BillNo as 工单 , bl_Name as 产线 ,wp_No as 料号 , wp_Name as 料名 ,wp_Model as 物料类型 ,MAX(W.mwf_CheckTime) AS 时间,
t.投产数 , SUM(CASE WHEN l.mbes_Result = 'NG' THEN 1 ELSE 0 END) AS 不良数,
convert(decimal(18,4),(case when SUM(CASE WHEN l.mbes_Result = 'NG' THEN 1 ELSE 0 END) =0 then 0
else SUM(CASE WHEN l.mbes_Result = 'NG' THEN 1 ELSE 0 END)/CONVERT(decimal(18,4),t.投产数 ) end)) as 不良率
FROM ( SELECT MAX(A.mwf_iFSort) AS iFSort , mwf_PID ,mad_BatchNo
FROM ( SELECT mwf_PID ,mad_BatchNo , mwf_iFSort FROM MES_WIPManuFlow
LEFT JOIN MES_MADetail ON mwf_BatchID = mad_BatchID
LEFT JOIN WMS_Materiel ON wp_ItemID = mad_ItemID
LEFT JOIN BAS_Line ON bl_ID = mwf_LineID
) A
GROUP BY mad_BatchNo ,mwf_PID
) B
LEFT JOIN MES_WIPManuFlow W ON W.mwf_PID = B.mwf_PID
AND B.iFSort = W.mwf_iFSort
LEFT JOIN MES_MADetail M ON mwf_BatchID = mad_BatchID
LEFT JOIN WMS_Materiel ON wp_ItemID = mad_ItemID
LEFT JOIN BAS_Line ON bl_ID = mwf_LineID
LEFT JOIN MES_LTQC L ON mbes_PID=B.mwf_PID
LEFT JOIN ( SELECT mad_BatchNo , COUNT(1) AS 投产数
FROM (SELECT MAX(A.mwf_iFSort) AS iFSort , mwf_PID , mad_BatchNo
FROM ( SELECT mwf_PID ,mad_BatchNo , mwf_iFSort FROM MES_WIPManuFlow
LEFT JOIN MES_MADetail ON mwf_BatchID = mad_BatchID
LEFT JOIN WMS_Materiel ON wp_ItemID = mad_ItemID
LEFT JOIN BAS_Line ON bl_ID = mwf_LineID
) A
GROUP BY mad_BatchNo , mwf_PID
) t
WHERE t.mwf_PID > 0
GROUP BY mad_BatchNo
) t ON t.mad_BatchNo = mad_BillNo

GROUP BY wp_No , bl_Name , wp_Name , B.mad_BatchNo , mad_BillNo ,wp_Model, t.投产数

表二的查询语句

select mbes_TestItem,SUM(case when mbes_Result='NG' then 1 else 0 end) as 不良数
from MES_WipManuFlow
left join MES_LTQC on mwf_PID=mbes_PID
LEFT JOIN MES_MADetail M ON mwf_BatchID = mad_BatchID
LEFT JOIN WMS_Materiel ON wp_ItemID = mad_ItemID
LEFT JOIN BAS_Line ON bl_ID = mwf_LineID
LEFT JOIN ( SELECT mad_BatchID , COUNT(1) AS 总和
FROM (SELECT MAX(A.mwf_iFSort) AS iFSort , mwf_PID , mad_BatchID
FROM ( SELECT mwf_PID ,mad_BatchID , mwf_iFSort FROM MES_WIPManuFlow
LEFT JOIN MES_MADetail ON mwf_BatchID = mad_BatchID
LEFT JOIN WMS_Materiel ON wp_ItemID = mad_ItemID
LEFT JOIN BAS_Line ON bl_ID = mwf_LineID
) A
GROUP BY mad_BatchID , mwf_PID
) t
WHERE t.mwf_PID > 0
GROUP BY mad_BatchID
) t ON t.mad_BatchID = mwf_BatchID
where mbes_Result='NG'
group by mbes_TestItem,t.总和

...全文

151 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

听雨停了 2017-09-20

打赏
举报



--测试数据

--表一

if object_id(N'Tempdb..#tab1') is not null

drop table #tab1

Create table #tab1(工单 varchar(20),产线 varchar(20),料号 varchar(50))

Insert #tab1

select 'BLT000001','A1-3','2.00.01.589.01' union all

select 'BLT000002','A1-3','2.00.01.589.01'

--表二(你的表一表二肯定有个字段用来关联的吧，这里假设用工单关联)

if object_id(N'Tempdb..#tab2') is not null

drop table #tab2

Create table #tab2(badname varchar(20),badqty INT,工单 varchar(20))

Insert #tab2

select 'QC20-30设置',6,'BLT000001' union all

select '设置-输出电压',1,'BLT000001' union all

select '输出电压-测试',1,'BLT000001' union all

select '输出电压-文波',1,'BLT000001'

--测试数据结束



--先把表二的数据行转列，然后关联表1显示

DECLARE @name VARCHAR(max),@sql VARCHAR(max)

set @name =stuff((SELECT DISTINCT ',['+badname+']'  from #tab2 for xml PATH('')),1,1,'')

set @sql ='SELECT b.*,a.* FROM (SELECT * from #tab2 pivot(max(badqty)for badname in('+@name+')) tab) a '--行转列

SET @sql=@sql +'right join #tab1 b on a.工单=b.工单'	--关联表1

PRINT @sql

EXEC(@sql)