广仁国网对接 SQL - 重工电子论坛

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

SELECT Physics2,matFactoryDate,mtsManufacturer,mtsPatch,tadQualityNum,lName,mtsNum,mfName,etName,mfSpec,

C,Si,Mn,P,S,V,Nb,Ti,YieldPoint,TensileStrength,Elongation,ImpactTest,ImpactTest1,ImpactTest2,mtDate as DETECTION_TIME,ItemStr3 as DETECTION_USER

FROM [fzyk1].[fzyk].dbo.q_materialTestReportView 

where  WriteDate > DATEADD(mm, -6, getdate()) and mfSpec='∠220*26'

GROUP BY Physics2,matFactoryDate,mtsManufacturer,mtsPatch,tadQualityNum,lName,mtsNum,mfName,etName,mfSpec,

C,Si,Mn,P,S,V,Nb,Ti,YieldPoint,TensileStrength,Elongation,ImpactTest,ImpactTest1,ImpactTest2,mtDate,ItemStr3 ORDER BY mtDate desc





SELECT * FROM [fzyk1].[fzyk].dbo.f_taskpart a INNER JOIN [fzyk1].[fzyk].dbo.F_MaterialDetail b ON a.mdid=b.mdID WHERE  a.scdID=45059 ORDER BY a.tpsectName,tpPartsID

BEGIN
SELECT * INTO #myTemp from (SELECT TOP 40000 row_number() over(ORDER BY feedingTime desc) as Num, machineName,feedingTime,[Value],WriteTime FROM [FZYK1].[superfzyk].dbo.TemperatureOfGalvanize ) as a;
INSERT INTO [FZYK1].[superfzyk].dbo.TemperatureOfGalvanize (machineName,feedingTime,[Value],WriteTime) SELECT machineName, DATEADD(minute,Num+Num-1, feedingTime) as feedingTime,[Value],DATEADD(minute,Num+Num-1, WriteTime) as WriteTime FROM #myTemp;
DROP TABLE #myTemp;
end

复制代码

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

SELECT gadQualityNum, gadHeatNum, barCode into #tmp FROM W_StockInDetail wsid INNER JOIN W_StockIn si ON si.siID = wsid.siID  INNER JOIN B_InOutType iot ON iot.iotID = si.iotID LEFT OUTER JOIN C_GoodsAcceptDetail cgad ON wsid.gadID = cgad.gadID WHERE Tag = '原材料' AND ISNULL(siZi,'') = '' AND iotName <> '退货出库'  







SELECT sidOldCode,'有' AS ifBack into #tmp2 FROM W_StockInDetail Group BY sidOldCode 



SELECT sodpaID, wsod.sodID,sodWeight,sodApplyCount,sodApplyWeight,sodPlanPrice,sodPlanMoney ,sodPrice,sodMoney,sodPriceNoTax,sodMoneyNoTax,sodRemark,wsod.soNum,iotID,soPickDept,soPickPerson,soTeam ,CASE sodCount WHEN 0 THEN 0 ELSE sodWeight/sodCount END AS sUnitWeight,CASE soDate WHEN '1900-1-1' THEN '' ELSE  CONVERT(VARCHAR(10),soDate,120) END AS soDate,soRed,Writer,sodProject,sodTowerType,sodTaskNum,sodProduceNum,sodcNum,CASE wsod.WriteDate WHEN '1900-1-1' THEN '' ELSE  CONVERT(VARCHAR(10),wsod.WriteDate,120) END AS WriteDate,soID,Locker,CASE wsod.LockDate WHEN '1900-1-1' THEN '' ELSE  CONVERT(VARCHAR(10),wsod.LockDate,120) END AS LockDate,wsod.State,soRemark,wsod.mfName,wsod.barCode,wsod.mfSpec,wsod.mfNum,wsod.gsName,wsod.zName,wsod.wID,wsod.wName,wsod.indexNum,wsod.iotName,wsod.mfID,wsod.etName,wsod.lName,wsod.mfUnit,wsod.mcName,sodLength,sodWidth,sodCount,Keeper,CASE OutDate WHEN '1900-1-1' THEN '' ELSE  CONVERT(VARCHAR(10),OutDate,120) END AS OutDate,sodDiameter,sodThick,sodRealWeight,sodUse ,wsod.mcNum,wsod.mfQuickNum,sodAccountType,mfcCode ,sodpaState ,approveState,CASE sodBackTime WHEN '1900-1-1' THEN '' ELSE  CONVERT(VARCHAR(10),sodBackTime,120) END AS sodBackTime,Valid,sodApplyID,sodrddDeal,sodrddPriceNew,sodUseDept ,gadQualityNum,gadHeatNum,ISNULL(ifBack,'') AS ifBack,isnull(t.soNum,'') as soApplyNum,ptTaskType into #tmp3  FROM W_StockOutView AS wsod LEFT OUTER JOIN  #tmp cgad ON wsod.barCode = cgad.barCode LEFT OUTER JOIN #tmp2  wsid2 ON wsid2 .sidOldCode = wsod.barCode  left join ( select soNum,sodid from W_StockPickDetail wspd left join W_StockPick wsp on wspd.soID = wsp.soID) t on t.sodid = wsod.sodapplyid  WHERE (1=1)  AND soApply = 0 AND Tag = '原材料'  AND Tag1 = ''  AND  sodproducenum LIKE '%20231209-01%' ORDER BY CASE Writer WHEN '' THEN 1 ELSE 2 END ASC, soDate DESC,sodID DESC 



select * from #tmp3  



drop table #tmp  



drop table #tmp2  



drop table #tmp3

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

SELECT Physics2,matFactoryDate,mtsManufacturer,mtsPatch,tadQualityNum,lName,mtsNum,mfName,etName,mfSpec, C,Si,Mn,P,S,V,Nb,Ti,Cr,YieldPoint,TensileStrength,Elongation,ImpactTest,ImpactTest1,ImpactTest2,mtDate as DETECTION_TIME,ItemStr3 as DETECTION_USER FROM [fzyk1].[fzyk].dbo.q_materialTestReportView  where mtDate > DATEADD(mm, -60, getdate()) GROUP BY Physics2,matFactoryDate,mtsManufacturer,mtsPatch,tadQualityNum,lName,mtsNum,mfName,etName,mfSpec, C,Si,Mn,P,S,V,Nb,Ti,Cr,YieldPoint,TensileStrength,Elongation,ImpactTest,ImpactTest1,ImpactTest2,mtDate,ItemStr3 ORDER BY mtDate desc