一个查询问题

[mailto520]

table1
id bankID bankNO user bankName
1 z0001 622588781 张三 招行
2 z0002 40636612 李四 建行
3 z0003 51871005 王四 工行

table2
id bankOutID bankInID turnMoney
1 z0001 z0002 200
2 z0003 z0001 500

查询结果如下:
totalTable
id bankOutID bankNoOut UserOut bankNameOut bankInID bankNoIn UserIn turnMoney
1 z0001 622588781 张三 招行 z0002 40636612 李四 200
2 z0003 51871005 王四 工行 z0001 622588781 张三 500

[dawugui]

create table table1(id int, bankID varchar(10) , bankNO varchar(10), [user] varchar(10), bankName varchar(10))
insert into table1 values(1 , 'z0001' ,'622588781', '张三' , '招行') 
insert into table1 values(2 , 'z0002' ,'40636612' , '李四' , '建行') 
insert into table1 values(3 , 'z0003' ,'51871005' , '王四' , '工行') 
go
create table table2(id int, bankOutID varchar(10) , bankInID varchar(10) , turnMoney int)
insert into table2 values(1 , 'z0001' , 'z0002' , 200) 
insert into table2 values(2 , 'z0003' , 'z0001' , 500)
go

select 
  m.id ,
  m.bankOutID ,
  bankNoOut   = (select bankNO from table1 where bankID = m.bankOutID),
  UserOut     = (select [user] from table1 where bankID = m.bankOutID),
  bankNameOut = (select bankName from table1 where bankID = m.bankOutID),
  m.bankInID ,
  bankNoIn    = (select bankNO from table1 where bankID = m.bankInID),
  UserIn      = (select [user] from table1 where bankID = m.bankInID),
  m.turnMoney
from table2 m

drop table table1 , table2 

/*
id          bankOutID  bankNoOut  UserOut    bankNameOut bankInID   bankNoIn   UserIn     turnMoney   
----------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- 
1           z0001      622588781  张三         招行          z0002      40636612   李四         200
2           z0003      51871005   王四         工行          z0001      622588781  张三         500

（所影响的行数为 2 行）
*/

[ghs79]

select a.id, a.bankoutid, b.bankno banknoout, b.[user] userout, bankname banknameout,
a.bankinid, b.bankno banknoin, [user] userid, turnMoney
from table2 a left join table1 b
on a.bankinid = b.bankid

[ws_hgo]

if object_id('table1') is not null drop table table1
create table table1(id int, bankID varchar(10) , bankNO varchar(10), [user] varchar(10), bankName varchar(10))
insert into table1 values(1 , 'z0001' ,'622588781', '张三' , '招行') 
insert into table1 values(2 , 'z0002' ,'40636612' , '李四' , '建行') 
insert into table1 values(3 , 'z0003' ,'51871005' , '王四' , '工行') 
go
create table table2(id int, bankOutID varchar(10) , bankInID varchar(10) , turnMoney int)
insert into table2 values(1 , 'z0001' , 'z0002' , 200) 
insert into table2 values(2 , 'z0003' , 'z0001' , 500)
go

select B2.id,B2.bankOutID,B2.bankNO,B2.[user] 'UserOut',B2.bankName,B1.bankID 'bankInID',B1.bankNO,B1.[user] 'UserIn',B2.turnMoney from table1 B1 join
(select B2.id,B2.bankOutID,B2.turnMoney,B2.bankInID,B1.bankName,B1.bankNO,B1.[user] from table1 B1 join table2 B2 on
B2.bankOutID=B1.bankID) B2 on B2.bankInID=B1.bankID

id          bankOutID  bankNO     UserOut    bankName   bankInID   bankNO     UserIn     turnMoney
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
1           z0001      622588781  张三         招行         z0002      40636612   李四         200
2           z0003      51871005   王四         工行         z0001      622588781  张三         500

(2 行受影响)

[xiangyu9999]

等值连接也行：
select a.id, a.bankoutid, b.bankno banknoout,
b.user userout, a.bankname banknameout,
a.bankinid, b.bankno banknoin,
user userid, a.turnMoney
from table2 a left join table1 b
on a.bankinid = b.bankid

[ye_zhi_gen_bo_luo_mi]

select a.id,a.bankid,a.bankno,a.bankname,a.[user],b.bankid,b.bankno,b.[user],c.turnmoney
from table1 a,table1 b,table2 c
where a.bankid=c.bankoutid and b.bankid=c.bankinid

[ljhcy99]

SELECT A4.id,A4.bankOutID,A4.bankNoOut,A4.UserOut,A4.bankName,A4.bankInID,A3.bankNO AS bankInID,A3.userA AS USERIN,A4.turnMoney
FROM TABLE1 AS A3,

(SELECT A2.id,A2.bankOutID,A1.bankNO AS bankNoOut,A1.userA UserOut ,A1.bankName ,A2.bankInID,turnMoney
FROM TABLE1 AS A1, TABLE2 AS A2
WHERE A1.bankID=A2.bankOutID
) A4
WHERE A3.bankID=A4.bankInID