请问如何实现这个查询
有表a、b、c如下
a：
id otherid
1 b1
2 b2
3 c2
b:
id bname
1 路人甲
2 路人乙
c:
id cname
1 路人丙
2 路人丁
现希望在a表中otherid以b开头的就可以和bname关联，c开头的和cname关联
即查询出如下的结果：
id otherid name
1 b1 路人甲
2 b2 路人乙
3 c2 路人丁
[解决办法]
declare @ta table(id int, otherid varchar(10))
insert @ta
select 1, 'b1 ' union all
select 2, 'b2 ' union all
select 3, 'c2 '
declare @tb table(id int, bname varchar(10))
insert @tb
select 1, '路人甲 ' union all
select 2, '路人乙 '
declare @tc table(id int, cname varchar(10))
insert @tc
select 1, '路人丙 ' union all
select 2, '路人丁 '
select a.id,a.otherid,isnull(b.bname,c.cname) as name
from @ta as a
left join @tb as b on a.otherid = 'b ' + rtrim(b.id)
left join @tc as c on a.otherid = 'c ' + rtrim(c.id)
/*结果
id otherid name
----------- ---------- ----------
1 b1 路人甲
2 b2 路人乙
3 c2 路人丁
*/
[解决办法]
select a.id,a.otherid,isnull(bname,cname) as name
from a left outer join b
on replace(a.otherid, 'b ', ' ')=b.id
left outer join c
on replace(a.otherid, 'c ', ' ')=c.id
[解决办法]
create table a (id int,otherid varchar(10))
insert a
select 1, 'b1 'union all
select 2, 'b2 'union all
select 3, 'c2 '
create table b (id int,bname varchar(10))
insert b
select 1, 'bskh 'union all
select 2, 'bsjh '
create table c (id int,cname varchar(10))
insert c
select 1, 'csdsh 'union all
select 2, 'ccdjh '
select a.id,a.otherid,sname = (case substring(otherid,1,1) when 'b ' then bname
else cname end ) from a,b,c
where cast(substring(otherid,2,1) as int) = b.id and cast(substring(otherid,2,1) as int) = c.id
drop table a
drop table b
drop table c
id otherid sname
----------- ---------- ----------
1 b1 bskh
2 b2 bsjh
3 c2 ccdjh
[解决办法]
select a.id,a.otherid,name=case when left(a.otherid,1)= 'b ' then b.otherid
when left(a.otherid,1)= 'c ' then c.otherid end from a left join b
on right(a.otherid,1)=rtrim(b.id) left join c on right(a.otherid,1)=rtrim(c.id)
