近期遇到最難的邏輯
if object_id('tempdb..#IL_Benson') is not null
drop table #IL_Benson ;
create table #IL_Benson (
ActSeq varchar(19) --18
, GCNo varchar(21) --21
, ActSeq_T varchar(19)
, GCNo_T varchar(21)
, RUN int default 0) ;
insert into #IL_Benson (ActSeq, GCNo)
values ('ACT1', 'A')
, ('ACT2', 'B'), ('ACT2', 'C'), ('ACT2', 'D')
, ('ACT3', 'A'), ('ACT3', 'C')
, ('ACT4', 'D')
, ('ACT5', 'D'), ('ACT5', 'E')
, ('ACT6', 'F'), ('ACT6', 'G')
, ('ACT7', 'F')
, ('ACT8', 'G'), ('ACT8', 'H')
, ('ACT9', 'Z')
/*
update a
set a.RUN = 0
, a.ActSeq_T = null
, a.GCNo_T = null
from #IL_Benson a
select a.ActSeq, a.GCNo, a.ActSeq_T, a.GCNo_T, a.RUN
from #IL_Benson a
order by 1, 2
*/
--資料檢測,RUN<0
--(RUN=-1) 1 ACT to 1 GC
update a
set a.ActSeq_T = a.ActSeq
, a.GCNo_T = a.GCNo
, a.RUN = -1
from #IL_Benson a
inner join (
select a.ActSeq, count(distinct a.GCNo) CNT
from #IL_Benson a
group by a.ActSeq
having count(distinct a.GCNo) = 1 ) b
on a.ActSeq = b.ActSeq
inner join (
select a.GCNo, count(distinct a.ActSeq) CNT
from #IL_Benson a
group by a.GCNo
having count(distinct a.ActSeq) = 1 ) c
on a.GCNo = c.GCNo
;
--(RUN=-2) 1 ACT to Multi GC and 1 GC to 1 ACT
update a
set a.ActSeq_T = a.ActSeq
, a.GCNo_T = b.GCNo_T
, a.RUN = -2
--select count(*)
from #IL_Benson a
inner join (
select a.ActSeq, count(distinct a.GCNo) CNT, min(a.GCNo) GCNo_T
from #IL_Benson a
group by a.ActSeq
having count(distinct a.GCNo) > 1 ) b
on a.ActSeq = b.ActSeq
inner join (
select b.ActSeq, count(distinct a.ActSeq) cnt
from #IL_Benson a
inner join (
select a.ActSeq, a.GCNo
from #IL_Benson a
inner join (
select a.ActSeq, count(distinct a.GCNo) CNT
from #IL_Benson a
group by a.ActSeq
having count(distinct a.GCNo) > 1 ) b
on a.ActSeq = b.ActSeq ) b
on a.GCNo = b.GCNo
group by b.ActSeq
having count(distinct a.ActSeq) = 1 ) c
on a.ActSeq = c.ActSeq
where a.ActSeq_T is null
and a.GCNo_T is null
;
--(RUN=-3) 1 ACT to 1 GC and 1 GC to Multi ACT
update a
set a.ActSeq_T = b.ActSeq_T
, a.GCNo_T = a.GCNo
, a.RUN = -3
--select count(*)
from #IL_Benson a
inner join (
select a.GCNo, count(distinct a.ActSeq) CNT, min(a.ActSeq) ActSeq_T
from #IL_Benson a
group by a.GCNo
having count(distinct a.ActSeq) > 1 ) b
on a.GCNo = b.GCNo
inner join (
select b.GCNo, count(distinct a.GCNo) cnt
from #IL_Benson a
inner join (
select a.ActSeq, a.GCNo
from #IL_Benson a
inner join (
select a.GCNo, count(distinct a.ActSeq) CNT
from #IL_Benson a
group by a.GCNo
having count(distinct a.ActSeq) > 1 ) b
on a.GCNo = b.GCNo ) b
on a.ActSeq = b.ActSeq
group by b.GCNo
having count(distinct a.GCNo) = 1
) c
on a.GCNo = c.GCNo
where a.ActSeq_T is null
and a.GCNo_T is null
;
--select * from #IL_Benson
--------------------------------------
--補衍伸關聯資料,RUN=1
DECLARE @RC INT
DECLARE @RUN INT
select @RC = count(*)
--select count(*)
from #IL_Benson a
where a.ActSeq_T is null
WHILE @RC > 0
BEGIN
SET @RC = 0
insert into #IL_Benson (ActSeq, GCNo, RUN)
select distinct b.ActSeq_T ActSeq, a.GCNo, 1
from #IL_Benson a
inner join (
select distinct b.ActSeq ActSeq_T, a.ActSeq, b.GCNo
from #IL_Benson a
inner join (
select distinct a.ActSeq, a.GCNo
from #IL_Benson a
where a.ActSeq_T is null
--and a.ActSeq = '3'
) b
on a.GCNo = b.GCNo ) b
on a.ActSeq = b.ActSeq
left outer join #IL_Benson c
on b.ActSeq_T = c.ActSeq
and a.GCNo = c.GCNo
and c.ActSeq_T is null
where a.ActSeq_T is null
and c.ActSeq is null
set @RC = @RC + @@ROWCOUNT
--select * from #IL_Benson where Run=1
insert into #IL_Benson (ActSeq, GCNo, RUN)
select distinct a.ActSeq, b.GCNo_T GCNo, 1
from #IL_Benson a
inner join (
select distinct b.GCNo GCNo_T, b.ActSeq, a.GCNo
from #IL_Benson a
inner join (
select distinct a.ActSeq, a.GCNo
from #IL_Benson a
where a.ActSeq_T is null
--and a.GCNo = 'D'
) b
on a.ActSeq = b.ActSeq ) b
on a.GCNo = b.GCNo
left outer join #IL_Benson c
on a.ActSeq = c.ActSeq
and b.GCNo_T = c.GCNo
and c.ActSeq_T is null
where a.ActSeq_T is null
and c.ActSeq is null
set @RC = @RC + @@ROWCOUNT
--select * from #IL_Benson where Run=1
--select a.RUN, count(*)
--from #IL_Benson a
--where a.ActSeq_T is null
--group by a.RUN
END
update a
set a.ActSeq_T = a.ActSeq
, a.GCNo_T = a.GCNo
from #IL_Benson a
where a.ActSeq_T is null
and a.GCNo_T is null
--select * from #IL_Benson order by ActSeq_T,GCNo_T
--------------------------------------
declare @RC INT
select @RC = count(*)
from #IL_Benson a
where a.RUN >= 0
--select @RC
WHILE @RC > 0
BEGIN
SET @RC = 0
update a
set a.ActSeq_T = b.ActSeq
--select count(*)
from #IL_Benson a
inner join (
select a.GCNo GCNo, min(a.ActSeq) ActSeq
from #IL_Benson a
where a.RUN >= 0
group by a.GCNo ) b
on a.GCNo_T = b.GCNo
and a.ActSeq_T <> b.ActSeq
where a.RUN >= 0
SET @RC = @RC + @@ROWCOUNT
update a
set a.GCNo_T = b.GCNo
--select count(*)
from #IL_Benson a
inner join (
select a.ActSeq ActSeq, min(a.GCNo) GCNo
from #IL_Benson a
where a.RUN >= 0
group by a.ActSeq ) b
on a.ActSeq_T = b.ActSeq
and a.GCNo_T <> b.GCNo
where a.RUN >= 0
SET @RC = @RC + @@ROWCOUNT
END
--select * from #IL_Benson order by ActSeq,GCNo
