求一SQL语句的写法？？

[happying2000]

现有两张数据表
tblA
id name sjid
01 A 0
02 B 0
0101 C 01
0102 D 01
0103 E 01
010101 F 0101
010102 G 0101
0201 H 02
020101 G 0201
03 H 0
0301 I 03
04 J 0


tblB
ZH id
test 010101
test 020101


请问如何通过这两张表产生以下结构的数据

id name sjid
01 A 0
0101 C 01
010101 E 0101
02 B 0
0201 H 02
020101 G 0201

[华夏小卒]

--> 测试数据: #tblA
if object_id('tempdb.dbo.#tblA') is not null drop table #tblA
go 
create table #tblA (id varchar(6),name varchar(1),sjid varchar(4))
insert into #tblA
select '01','A','0' union all
select '02','B','0' union all
select '0101','C','01' union all
select '0102','D','01' union all
select '0103','E','01' union all
select '010101','F','0101' union all
select '010102','G','0101' union all
select '0201','H','02' union all
select '020101','G','0201' union all
select '03','H','0' union all
select '0301','I','03' union all
select '04','J','0'
--> 测试数据: #tblB
if object_id('tempdb.dbo.#tblB') is not null drop table #tblB
go 
create table #tblB (ZH varchar(4),id varchar(6))
insert into #tblB
select 'test','010101' union all
select 'test','020101'

select distinct a.* 
from #tblA a,#tblB b where charindex(a.id,b.id)in(1,3,5)

id     name sjid
------ ---- ----
01     A    0
0101   C    01
010101 F    0101
02     B    0
0201   H    02
020101 G    0201

(6 行受影响)

[fpzgm]

with cte as (
select left(id,2) from tblB
union
select left(id,4) from tblB
union 
select id from tblB)

select t1.id,t2.name,t2.sjid
from cte t1
left join tblA t2 on t1.id=t2.id
order by t1.id

[abuying]

select * from tblA
where exists(select * from  tblb where substring(id,1,2)=substring(tblA.id,1,2) and left(id,2)=right(tblA.id,2))
order by len(id) asc