广仁 国网对接 SQL

李维强-15级

[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

李维强-15级

添加锌锅温度

1. 
   
2. BEGIN
   
3. 
   
4. 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;
   
5. 
   
6. 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;
   
7. 
   
8. DROP TABLE #myTemp;
   
9. 
   
10. end
    

复制代码

李维强-15级

国网在线问题文档
https://docs.qq.com/form/page/DZG5Nckp5VFpSaU5j

李维强-15级

[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