-- 建立表#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