求助sql 多个区间查询

[cnjuna]

主表a,a中的编号，1字母+10位编号，有一个表b是如图，，有的是区间的，如何查询b中的这些编号在a中有哪些，包括b的区间里的，可以把b表中的表增加起始，结尾两个字段，求助！

[听雨停了]

IF OBJECT_ID('tempdb..#tabA') IS NOT NULL
	DROP TABLE #tabA
CREATE TABLE #tabA(
	a VARCHAR(20)	
)
INSERT INTO #tabA
SELECT 'J2017001053' UNION ALL
SELECT 'J2017001054' UNION ALL
SELECT 'J2017001055' UNION ALL
SELECT 'J2017001056' UNION ALL
SELECT 'J2017001057' UNION ALL
SELECT 'J2017001058'

IF OBJECT_ID('tempdb..#tabB') IS NOT NULL
	DROP TABLE #tabB
CREATE TABLE #tabB(
	begin_a VARCHAR(20),
	end_a VARCHAR(20)	
)
INSERT INTO #tabB
SELECT 'J2017001053','J2017001057' UNION ALL
SELECT 'J2017001058',NULL
--测试数据结束

SELECT * FROM #tabB a
INNER JOIN #taba AS b ON LEFT(b.a,1)= LEFT(a.begin_a,1)
WHERE cast(RIGHT(b.a,LEN(b.a)-1) AS int) 
BETWEEN cast(RIGHT(a.begin_a,LEN(a.begin_a)-1) AS int) 
AND cast(RIGHT(isnull(a.end_a,a.begin_a),LEN(isnull(a.end_a,a.begin_a))-1) AS int)

大概写了下，是不是这样的意思啊

[cnjuna]

已经写出来了，还是非常感谢 -- duibudui 表格里的 数据样式 --iid 资产编号 起始 结尾 --32 S2017001519 S2017001519 S2017001519 --33 S2017001520 S2017001520 S2017001520 --34 S2017001521 -- S2017001522 S2017001521 S2017001522 --35 S2017001523 -- S2017001524 S2017001523 S2017001524 --36 S2017001525 -- S2017001540 S2017001525 S2017001540 CREATE TABLE #tcaiw5 (资产编号 varchar(50)) DECLARE @i INT SET @i=0 while @i <=508 --表单的最大行数 BEGIN INSERT INTO #tcaiw5 select 资产编号 FROM sbk_主表 WHERE 资产编号 between(SELECT 起始 FROM duibudui WHERE iid =@i) AND (SELECT 结尾 FROM duibudui WHERE iid =@i) set @i=@i+1 END --select * FROM #tcaiw5 --SELECT * FROM 主表 WHERE 资产编号 IN (select 资产编号 FROM #tcaiw5)

[ChinaITOldMan]

可以把B表的那个字段分为两个field处理，再关联 where between .. and ...