多个数据表联表查询

根据生产id查询出当天的生产记录

 

select  a.RobotNo as 机台,a.ProductCode as 生产CODE, a.ProducePlan as  计划数,b.ProductionCount_Total as 生产数,
a.Productivity as 生产性,d.StateText as 状态内容,c.BadnessCount as 不良数,a.Operator as 作业者,
a.Technician as 技术担当,a.Approver as 确认,a.Confirmor as 承认
from (select * from TouchDisplay.dbo.Table_ProductionMain where ProduceDate=CONVERT(varchar(100), GETDATE(), 23)) as a left join
TouchDisplay.dbo.Table_OutputReport as b on a.Tid=b.ProduceId left join 
(select ProduceId,SUM(BadnessCount) as BadnessCount from TouchDisplay.dbo.Table_BadnessReport group by ProduceId) as c on a.Tid=c.ProduceId left join
(select * from (select sr.Tid,sr.ProduceId,StateText from TouchDisplay.dbo.Table_StateInfo,TouchDisplay.dbo.Table_StateReport as sr where sr.StateNo=TouchDisplay.dbo.Table_StateInfo.StateNo ) as s where Tid=(select MAX(Tid) from (select sr.Tid,sr.ProduceId,StateText from TouchDisplay.dbo.Table_StateInfo,TouchDisplay.dbo.Table_StateReport as sr where sr.StateNo=TouchDisplay.dbo.Table_StateInfo.StateNo ) as ss where ss.ProduceId=s.ProduceId  )) as d
on a.Tid=d.ProduceId