tbluser
userid username
1 张三
2 李四 tbldeal
dealid dealuser1 dealuser2
1 1 2查询出
tbldeal
dealid dealusername1 dealusername2
1 张三 李四
userid username
1 张三
2 李四 tbldeal
dealid dealuser1 dealuser2
1 1 2查询出
tbldeal
dealid dealusername1 dealusername2
1 张三 李四
dealusername1=(select username from tbluser where userid =dealuser1),
dealusername2 =(select username from tbluser where userid =dealuser2)
from tbldeal
a.dealid,
b.username as dealusername1,
c.username as dealusername2
from
tbldeal a
left join
tbluser b
on
a.dealuser1=b.userid
left join
tbluser c
on
a.dealuser2=c.userid
INSERT @TA
SELECT 1, N'张三' UNION ALL
SELECT 2, N'李四'DECLARE @TB TABLE([dealid] INT, [dealuser1] INT, [dealuser2] INT)
INSERT @TB
SELECT 1, 1, 2select dealid,
dealusername1=(select username from @TA where userid =dealuser1),
dealusername2 =(select username from @TA where userid =dealuser2)
from @TB /*
dealid dealusername1 dealusername2
----------- ------------- -------------
1 张三 李四
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-16 10:45:21
----------------------------------------------------------------
--> 测试数据:[tbluser]
if object_id('[tbluser]') is not null drop table [tbluser]
create table [tbluser]([userid] int,[username] varchar(4))
insert [tbluser]
select 1,'张三' union all
select 2,'李四'
--> 测试数据:[tbldeal]
if object_id('[tbldeal]') is not null drop table [tbldeal]
create table [tbldeal]([dealid] int,[dealuser1] int,[dealuser2] int)
insert [tbldeal]
select 1,1,2
------开始查询:select dealid,
dealusername1=(select username from tbluser where userid =dealuser1),
dealusername2 =(select username from tbluser where userid =dealuser2)
from tbldeal
------查询结果:
/*-------------------------------------------------------------
dealid dealusername1 dealusername2
1 张三 李四
*/-------------------------------------------------------------
insert @T1
select 1, 1, 2declare @T2 table([userid] int, [username] nvarchar(2))
insert @T2
select 1, '张三' union all
select 2, '李四'select a.dealid,b.username as dealusername1,c.username as dealusername2
from @T1 a
left join @T2 b on a.dealuser1=b.userid
left join @T2 c on a.dealuser2=c.useriddealid dealusername1 dealusername2
----------- ------------- -------------
1 张三 李四
(1 行受影响)支持用连接