34,588
社区成员
发帖
与我相关
我的任务
分享
---try
declare @tbisnprocess table (indexid int,sn int,processid int,status int,date datetime)
insert into @tbisnprocess select 1,'200101',1,1,'2009-1-1'
union all select 2,'200101',2,-1,'2009-1-2'
union all select 3,'200101',3,0,'2009-1-3'
union all select 4,'200101',0,0,'2009-1-3'
union all select 1,'200102',1,1,'2009-1-1'
union all select 2,'200102',2,1,'2009-1-2'
union all select 3,'200102',3,1,'2009-1-3'
union all select 4,'200102',0,0,'2009-1-3'
union all select 1,'200103',1,1,'2009-1-1'
union all select 2,'200103',2,1,'2009-1-2'
union all select 3,'200103',3,0,'2009-1-3'
union all select 4,'200103',0,0,'2009-1-3'
declare @tblProcess table (processid int,processName nvarchar(100))
insert into @tblProcess select 1,'工序1'
union all select 2,'工序2'
union all select 3,'工序3'
select sn,max(processName) from
(select b.processid,sn,processName= case when status=0 and exists(select processid from @tbisnprocess where a.sn=sn
and a.indexid=indexid+1 and processid='3') then '完工' when exists(select status from @tbisnprocess where a.sn=sn
and a.indexid=indexid+1 and status=-1)
then '维修' when exists(select status from @tbisnprocess where a.sn=sn
and a.indexid=indexid+1 and status=1) then
'工序3' end
from @tbisnprocess a join @tblprocess b on a.processid=b.processid) tb group by sn
(3 行受影响)
sn
----------- -----
200101 维修
200102 工序3
200103 工序3
不是很明白楼主的意思:
if (object_id('tempdb..#tblSNProcess') > 0)
drop table #tblSNProcess
create table #tblSNProcess(indexID varchar(10), SN int,ProcessID varchar(10),status int,date varchar(20))
insert #tblSNProcess
select '1','200101','1',1,'2009-1-1' union
select '2','200101','2',-1,'2009-1-2' union
select '3','200101','3',0,'2009-1-3' union
select '4','200101','0',0,'2009-1-3' union
select '1','200102','1',1,'2009-1-1' union
select '2','200102','2',1,'2009-1-2' union
select '3','200102','3',1,'2009-1-3' union
select '4','200102','0',0,'2009-1-3' union
select '1','200103','1',1,'2009-1-1' union
select '2','200103','2',1,'2009-1-2' union
select '3','200103','3',0,'2009-1-3' union
select '4','200103','0',0,'2009-1-3'
if (object_id('tempdb..#tblProcess') > 0)
drop table #tblProcess
create table #tblProcess(ProcessID varchar(10),ProcessName varchar(20))
insert #tblProcess
select '1','工序1' union
select '2','工序2' union
select '3','工序3'
select T1.SN,case sum(T1.status) when 0 then '維修'
when 3 then '完工'
when 2 then (select distinct T3.Processname from #tblProcess T3
join #tblSNProcess T4 on(T4.ProcessID = T3.ProcessID and T4.ProcessID =3)) end '下道工序'
from #tblSNProcess T1
left join #tblProcess T2 on (T1.ProcessID = T2.ProcessID)
group by T1.SN
order by T1.SN
SN 下道工序
----------- --------------------
200101 維修
200102 完工
200103 工序3
create table tblSNProcess(indexID int, SN int,ProcessID int,status int,date datetime)
insert tblSNProcess select 1, 200101, 1, 1, '2009-1-1'
insert tblSNProcess select 2, 200101, 2, -1, '2009-1-2'
insert tblSNProcess select 3, 200101, 3, 0 ,'2009-1-3'
create table tblProcess(ProcessID int,ProcessName varchar(10))
insert tblProcess select 1 ,'工序1'
insert tblProcess select 2 ,'工序2'
insert tblProcess select 3 ,'工序3'
select b.ProcessName,A.SN,
(select case when c.status=-1 then '维修'
else rtrim(c.status) end 'status'
from tblSNProcess c where C.ProcessID=A.ProcessID-1) AS DD
from tblSNProcess a left join tblProcess b on
a.ProcessID=b.ProcessID
WHERE A.STATUS=0
select case when exists(select 1 from #tblSNProcess b where a.SN=b.SN and a.ProcessID=b.ProcessID+1 and b.status=-1) then '维修'
else (select ProcessName from #tblProcess where a.ProcessID=ProcessID) end as 工序
from #tblSNProcess a
where status=0
and not exists(select 1 from #tblSNProcess b where a.status=b.status and a.date<b.date)
工序
----------
维修
(1 行受影响)
select B.ProcessID from tblSNProcess A join tblProcess B on A.ProcessID = B.ProcessID where A.status = '0'
create table #tblSNProcess(indexID int, SN int,ProcessID int,status int,date datetime)
insert #tblSNProcess select 1, 200101, 1, 1, '2009-1-1'
insert #tblSNProcess select 2, 200101, 2, -1, '2009-1-2'
insert #tblSNProcess select 3, 200101, 3, 0 ,'2009-1-3'
create table #tblProcess(ProcessID int,ProcessName varchar(10))
insert #tblProcess select 1 ,'工序1'
insert #tblProcess select 2 ,'工序2'
insert #tblProcess select 3 ,'工序3'
select case when exists(select 1 from #tblSNProcess b where a.SN=b.SN and a.ProcessID=b.ProcessID+1 and b.status=-1) then '维修'
else (select ProcessName from #tblProcess where a.ProcessID=ProcessID) end as 工序
from (
select *,row_number() over(partition by status order by date desc) as rn from #tblSNProcess
) a where status=0 and rn=1
工序
----------
维修
(1 行受影响)