Jason's 程設BLOG

近期遇到最難的邏輯

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