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
--> 测试数据:[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,2select 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 财务 市场 */
from 调拨表 a
left join 部门表 b on a.bm_id1=b.dpt_id
left join 部门表 c on a.bm_id2=c.dpt_id
--> 测试数据:[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,2select
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 财务 市场
*/