数据表查询问题
如下所示，有表A和表B：
A表结构：
ID Respondents_No Investigators_No
1 N_10001 N_10011
2 N_10001 N_10012
3 N_10002 N_10013
4 N_10002 N_10014
5 N_10003 N_10011
6 N_10003 N_10012
7 N_10004 N_10013
8 N_10004 N_10014
B表结构：
ID Emp_NO Emp_Name Emp_Level
1 N_10001 David.Li B-1
2 N_10002 Kate.Zhang B-1
3 N_10003 Lucy.Chen B-1
4 N_10004 Lily.Wu B-1
5 N_10011 Tom.Yang C-1
6 N_10012 Snow.Zhu C-1
7 N_10013 Smile.Ma C-1
8 N_10014 David.Liu C-1
其中B表的Emp_NO和A表中的Respondents_No，Investigators_No为员工编号。
现在想如何能通过sql语句得到以下表结构：
ID Respondents_No Respondents_Name Respondents_No Respondents_Name
1 N_10001 David.Li N_10011 Tom.Yang
2 N_10001 David.Li N_10012 Snow.Zhu
3 N_10002 Kate.Zhang N_10013 Smile.Ma
4 N_10002 Kate.Zhang N_10014 David.Liu
5 N_10003 Lucy.Chen N_10011 Tom.Yang
6 N_10003 Lucy.Chen N_10012 Snow.Zhu
7 N_10004 Lily.Wu N_10013 Smile.Ma
8 N_10004 Lily.Wu N_10014 David.Liu
SQL 查询?多表关联
[解决办法]
if object_id('[TBA]') is not null drop table [TBA]
go
create table [TBA] (ID int,Respondents_No nvarchar(14),Investigators_No nvarchar(14))
insert into [TBA]
select 1,'N_10001','N_10011' union all
select 2,'N_10001','N_10012' union all
select 3,'N_10002','N_10013' union all
select 4,'N_10002','N_10014' union all
select 5,'N_10003','N_10011' union all
select 6,'N_10003','N_10012' union all
select 7,'N_10004','N_10013' union all
select 8,'N_10004','N_10014'
if object_id('[TBB]') is not null drop table [TBB]
go
create table [TBB] (ID int,Emp_NO nvarchar(14),Emp_Name nvarchar(20),Emp_Level nvarchar(6))
insert into [TBB]
select 1,'N_10001','David.Li','B-1' union all
select 2,'N_10002','Kate.Zhang','B-1' union all
select 3,'N_10003','Lucy.Chen','B-1' union all
select 4,'N_10004','Lily.Wu','B-1' union all
select 5,'N_10011','Tom.Yang','C-1' union all
select 6,'N_10012','Snow.Zhu','C-1' union all
select 7,'N_10013','Smile.Ma','C-1' union all
select 8,'N_10014','David.Liu','C-1'
select * from [TBA]
select * from [TBB]
SELECT A.Respondents_No ,
( SELECT emp_name
FROM TBB B
WHERE B.emp_no = A.Respondents_No
) AS Respondents_name ,
A.Investigators_No ,
( SELECT emp_name
FROM TBB B
WHERE B.emp_no = A.Investigators_No
) AS Investigators_name
FROM [TBA] A
/*
Respondents_NoRespondents_nameInvestigators_NoInvestigators_name
N_10001David.LiN_10011Tom.Yang
N_10001David.LiN_10012Snow.Zhu
N_10002Kate.ZhangN_10013Smile.Ma
N_10002Kate.ZhangN_10014David.Liu
N_10003Lucy.ChenN_10011Tom.Yang
N_10003Lucy.ChenN_10012Snow.Zhu
N_10004Lily.WuN_10013Smile.Ma
N_10004Lily.WuN_10014David.Liu*/