22,206
社区成员
发帖
与我相关
我的任务
分享
create table T_Signla
(sInputDesc varchar(20),sSignalPoint varchar(20),iSignalPointNUM varchar(10),sStopPoint varchar(20),iStopNUM varchar(10))
insert into T_Signla
select '第一生产线','请求停线1','S1','实际停线1','S11' union all
select '第二生产线','请求停线2','S2','实际停线2','S12' union all
select '第三生产线','请求停线3','S3','实际停线3','S13' union all
select '第四生产线','请求停线4','S4','实际停线4','S14' union all
select '第五生产线','请求停线5','S5','实际停线5','S15' union all
select '第六生产线','请求停线6','S6','实际停线6','S16'
create table T_StopTime
(sNum varchar(10),tStart varchar(20),tEnd varchar(20))
insert into T_StopTime
select 's1','2019-5-16 12:30','2019-5-16 12:40' union all
select 'S11','2019-5-16 12:35','2019-5-16 12:39' union all
select 's2','2019-5-16 13:30','2019-5-16 13:40' union all
select 'S12','2019-5-16 13:33','2019-5-16 13:39' union all
select 'S1','2019-6-16 13:33','2019-6-16 13:39' union all
select 'S2','2019-6-16 14:33','2019-6-16 14:39'
select 生产线=b.sInputDesc,
请求信号=b.sSignalPoint,
请求时间=a.tStart,
停线时间=isnull((select top 1 c.tStart
from T_StopTime c
inner join T_Signla d on c.sNum=d.iStopNUM
where d.sInputDesc=b.sInputDesc
and c.tStart between a.tStart and a.tEnd),''),
请求结束=a.tEnd
from T_StopTime a
inner join T_Signla b on a.sNum=b.iSignalPointNUM
order by a.tStart
/*
生产线 请求信号 请求时间 停线时间 请求结束
-------------------- -------------------- -------------------- -------------------- --------------------
第一生产线 请求停线1 2019-5-16 12:30 2019-5-16 12:35 2019-5-16 12:40
第二生产线 请求停线2 2019-5-16 13:30 2019-5-16 13:33 2019-5-16 13:40
第一生产线 请求停线1 2019-6-16 13:33 2019-6-16 13:39
第二生产线 请求停线2 2019-6-16 14:33 2019-6-16 14:39
(4 行受影响)
*/
if object_id(N'tempdb.dbo.#T_Signla') is not null
drop table #T_Signla
go
create table #T_Signla
(sInputDesc varchar(20),sSignalPoint varchar(20),iSignalPointNUM varchar(10),sStopPoint varchar(20),iStopNUM varchar(10))
insert into #T_Signla
select '第一生产线','请求停线1','S1','实际停线1','S11' union all
select '第二生产线','请求停线2','S2','实际停线2','S12' union all
select '第三生产线','请求停线3','S3','实际停线3','S13' union all
select '第四生产线','请求停线4','S4','实际停线4','S14' union all
select '第五生产线','请求停线5','S5','实际停线5','S15' union all
select '第六生产线','请求停线6','S6','实际停线6','S16'
if object_id(N'tempdb.dbo.#T_StopTime') is not null
drop table #T_StopTime
go
create table #T_StopTime
(sNum varchar(10),tStart DATETIME,tEnd DATETIME)
insert into #T_StopTime
select 's1','2019-5-16 12:30','2019-5-16 12:40' union all
select 'S11','2019-5-16 12:35','2019-5-16 12:39' union all
select 's2','2019-5-16 13:30','2019-5-16 13:40' union all
select 'S12','2019-5-16 13:33','2019-5-16 13:39' union all
select 'S1','2019-6-16 13:33','2019-6-16 13:39' union all
select 'S2','2019-6-16 14:33','2019-6-16 14:39'
SELECT B.sInputDesc,B.sSignalPoint,A.tStart,C.tStart,A.tEnd
FROM #T_StopTime A
JOIN #T_Signla B ON A.sNum=B.iSignalPointNUM
OUTER APPLY (SELECT TOP 1 tStart FROM #T_StopTime WHERE B.iStopNUM=sNum AND tStart>=A.tStart ORDER BY tStart) AS C