select
a.InfoID,
b.UserName as InputUserName,
c.UserName as UpdateUserName,
a.InputUserID,
a.UpdateUserID
from
表2 as a
join
表1 as b on a.InputUserID=b.UserID
join
表1 as c on a.UpdateUserID=c.UserID
a.InfoID,
b.UserName as InputUserName,
c.UserName as UpdateUserName,
a.InputUserID,
a.UpdateUserID
from
表2 as a
join
表1 as b on a.InputUserID=b.UserID
join
表1 as c on a.UpdateUserID=c.UserID
if object_id('tempdb.dbo.#User') is not null drop table #User
create table #User (UserID int,UserName varchar(4))
insert into #User
select 1,'张三' union all
select 2,'李四'
--> 测试数据: #Info
if object_id('tempdb.dbo.#Info') is not null drop table #Info
create table #Info (InfoID int,InputUserID int,UpdateUserID int)
insert into #Info
select 1,1,2 union all
select 2,2,1select
a.InfoID,
b.UserName as InputUserName,
c.UserName as UpdateUserName,
a.InputUserID,
a.UpdateUserID
from
#Info as a
join
#User as b on a.InputUserID=b.UserID
join
#User as c on a.UpdateUserID=c.UserID/*
InfoID InputUserName UpdateUserName InputUserID UpdateUserID
----------- ------------- -------------- ----------- ------------
1 张三 李四 1 2
2 李四 张三 2 1
*/
select infoid,inputname=b.username,updatename=c.username,InputUserID,UpdateUserID
from 表2 a
left join 表1 b on a.inputuserid=b.userid
left join 表1 c on a.updateuserid=c.userid
2、再多一个审批人 ApprovalUserID,当然要join 多一次 User 表
(select UserName from [User] where UserID = t.InputUserID) as InputUserName,
(select UserName from [User] where UserID = t.UpdateUserID) as UpdateUserName,
InputUserID,
UpdateUserID
from Info t
insert into [User]
select 1,'张三' union all
select 2,'李四'
create table Info (InfoID int,InputUserID int,UpdateUserID int)
insert into Info
select 1,1,2 union all
select 2,2,1select InfoID,
(select UserName from [User] where UserID = t.InputUserID) as InputUserName,
(select UserName from [User] where UserID = t.UpdateUserID) as UpdateUserName,
InputUserID,
UpdateUserID
from Info tdrop table [user] , info/*
InfoID InputUserName UpdateUserName InputUserID UpdateUserID
----------- ------------- -------------- ----------- ------------
1 张三 李四 1 2
2 李四 张三 2 1(所影响的行数为 2 行)
*/