需要批量调取表 #t4里的RN1\RN2\RN3 快速在#TBB查找符合要求的结果，请专家解答

Question

 --  建立表#T1 #T2 #T3  ，实际为已知数据。
 DROP TABLE  #T1
 SELECT * INTO #T1 FROM(
select '2809'as MID,'1'as RN1
union all select '2021','3'
union all select '1001','6'
union all select '2034','2'
union all select '4004','4'
)t

DROP TABLE  #T2
 SELECT * INTO #T2 FROM(
select '4004'TID,'1'RN2
union all select '2321','4'
union all select '1001','3'
union all select '2134','2'
union all select '2809','5'
)t

DROP TABLE   #t3
 SELECT * INTO #T3 FROM(
select '4004'KID,'3'RN3
union all select '2809','6'
union all select '1001','4'
union all select '2809','2'
union all select '1909','1'
)t

-- 表 #t4 是 需要调用的条件 RN1\RN2\RN3，实际ID有700行记录
DROP TABLE   #t4
 SELECT * INTO #T4 FROM(
select '1'ID,'6'RN1,'3'RN2,'4'RN3
union all select '2','1','5','6'
union all select  '3','4','1','3'
union all select  '4','2','4','4'
)t

--表 #tBB是已知的大数据表，有2700行记录
DROP TABLE   #tBB
 SELECT * INTO #TBB FROM(
select '1'ID,'1001'SNO
union all select  '2','2809'
union all select  '3','1003'
union all select  '4','4004'
)t

-- 以下代码是笨办法，仅仅对付几行数据。问题：如何批量调取表 #t4里的RN1\RN2\RN3 快速在#TBB查找符合要求的结果
select * from #TBB 
where 1=1
and sno in(select MID FROM #t1 where RN1=6) -- 表 #t4里ID=1的值
and sno in(select TID FROM #t2 where RN2=3)
and sno in(select KID FROM #t3 where RN3=4)
union all
select * from #TBB 
where 1=1
and sno in(select MID FROM #t1 where RN1=1) -- 表 #t4里ID=2的值
and sno in(select TID FROM #t2 where RN2=5)
and sno in(select KID FROM #t3 where RN3=6)
union all
select * from #TBB 
where 1=1
and sno in(select MID FROM #t1 where RN1=4) -- 表 #t4里ID=13的值
and sno in(select TID FROM #t2 where RN2=1)
and sno in(select KID FROM #t3 where RN3=3)
union all
select * from #TBB 
where 1=1
and sno in(select MID FROM #t1 where RN1=2) -- 表 #t4里ID=4的值
and sno in(select TID FROM #t2 where RN2=4)
and sno in(select KID FROM #t3 where RN3=4)

想要的结果

ID SNO
1 1001
2 2809
4 4004

Answer 1

SELECT  *
FROM    #TBB
WHERE   SNO IN (   SELECT   a.MID
                   FROM     #T1 a
                            INNER JOIN #T2 b ON a.MID = b.TID
                            INNER JOIN #T3 c ON a.MID = c.KID
                            INNER JOIN #T4 d ON a.RN1 = d.RN1 AND   b.RN2 = d.RN2 AND   c.RN3 = d.RN3 );