select a.db_id,b.dpt_name,c.dpt_name
from 调拨表 a
left join 部门表 b on a.bm_id1=b.dpt_id
left join 部门表 c on a.bm_id2=c.dpt_id

------解决方案--------------------

SQL code

--> 测试数据：[a1]
if object_id('[a1]') is not null drop table [a1]
create table [a1]([dpt_id] int,[dpt_name] varchar(4))
insert [a1]
select 1,'客服' union all
select 2,'市场' union all
select 3,'财务'
--> 测试数据：[b2]
if object_id('[b2]') is not null drop table [b2]
create table [b2]([db_id] int,[bm_id1] int,[bm_id2] int)
insert [b2]
select 1001,1,2 union all
select 1002,1,3 union all
select 1003,3,2

select 
b.[db_id],a.dpt_name as namea,c.dpt_name as namec
from b2 b
inner join a1 a
on b.bm_id1=a.dpt_id
inner join a1 c
on b.bm_id2=c.dpt_id

/*
db_id    namea    namec
1001    客服    市场
1002    客服    财务
1003    财务    市场
*/