考題

問題：
台北市某國小四年級月考題目

  A B C D E
X             A
--------------
E E E E E E

請問ABCDE各代表那一個阿拉伯數字….(條件:數字不能重覆)

IF OBJECT_ID('tempdb..#TMP') is not null
	drop table #TMP

select '0' val 
into #TMP --產生0~9的結果集
union 
select '1'
union 
select '2'
union 
select '3'
union 
select '4'
union 
select '5'
union 
select '6'
union 
select '7'
union 
select '8'
union 
select '9'

Declare @result BIT = 0 --1:true / 0:false
Declare @CNT INT = 0

IF OBJECT_ID('tempdb..#TMP_3') is not null
	begin
		DROP TABLE #TMP_3
		
		select '0' A
			,'1' B
			,'2' C
			,'3' D 
			,'4' E 
			,'01234' X
		into #TMP_3	 --所有符合的結果		
	end
	

while @result <> 1
	BEGIN
		IF OBJECT_ID('tempdb..#TMP_1') is not null
			DROP TABLE #TMP_1					
		
		SELECT top 5 val 
			, IDENTITY(INT,1,1) RID
		into #TMP_1 --亂數取得5筆
		FROM #TMP
		ORDER BY NEWID()			
		
		
		IF OBJECT_ID('tempdb..#TMP_2') is not null
			DROP TABLE #TMP_2
			
		select [1] as '1'
			, [2] as '2'
			, [3] as '3'
			, [4] as '4'
			, [5] as '5'					
		into #TMP_2 --改為橫式
		from
			(select val,rid from #TMP_1) as p
		pivot
			(
			max(val)
			for rid in ([1], [2], [3], [4], [5]) 
			) as pt
		
		--select * from #TMP_3 order by X
		
		insert into #TMP_3
		select *, [1]+[2]+[3]+[4]+[5]
		from #TMP_2 T
		where T.[1]+T.[2]+T.[3]+T.[4]+T.[5] not in (select X from #TMP_3)
			
		if @@ROWCOUNT = 0 
		begin 
			set @CNT = @CNT+1
		end 
		
		if @CNT>50 --若找不到50次則EXIT
		begin
			set @result = 1 
		end
		
	END
	
select *									
from #TMP_3 
where [A]*(([A]*10000) + ([B]*1000) + ([C]*100) + ([D]*10) + [E]) = [E]*111111 

--輸出結果
select 7*((7*10000) + (9*1000) + (3*100) + (6*10) + 5)