if not object_id('A') is null
drop table A
Go
create table A
(id int,user1 int,user2 int,user3 int,pro1 int,pro2 int,por3 int)
insert into A values(1,2,1,2,1,1,1)
insert into A values(2,1,2,3,1,2,2)
if not object_id('B') is null
drop table B
Go
create table B
(id int ,user_name varchar(50))
insert into B values(1,'张三')
insert into B values(2,'李四')
insert into B values(3,'王麻子')
if not object_id('C') is null
drop table C
Go
create table C
(id int ,pro_name varchar(50))
insert into C values(1,'魔兽')
insert into C values(2,'星际')
insert into C values(3,'cs')
select A.id,T.user_name,S.user_name,J.user_name,H.pro_name,M.pro_name,N.pro_name from A
join B T on A.user1=T.id
join B S on A.user2=S.id
join B J on A.user3=J.id
join C H on A.pro1=H.id
join C M on A.pro1=M.id
join C N on A.pro1=N.id
ORDER BY A.id
id user_name user_name user_name pro_name pro_name pro_name
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 李四 张三 李四 魔兽 魔兽 魔兽
2 张三 李四 王麻子 魔兽 魔兽 魔兽
(2 行受影响)