3,491
社区成员
发帖
与我相关
我的任务
分享
[TEST@ora10gr1#2009-11-26/21:45:30] SQL>with
2 user1 as( select 1 id,'user1a' name from dual
3 union all
4 select 2 id,'user1b' name from dual
5 ),
6 user2 as( select 3 id,'user2a' name from dual
7 union all
8 select 4 id,'user2b' name from dual
9 ),
10 user3 as( select 5 id,'user3a' name from dual
11 union all
12 select 6 id,'user3b' name from dual
13 ),
14 userStatus as( select 1 user_id from dual
15 union all
16 select 2 user_id from dual
17 union all
18 select 3 user_id from dual
19 union all
20 select 4 user_id from dual
21 union all
22 select 5 user_id from dual
23 union all
24 select 6 user_id from dual
25 )
26 select d.user_id,a.name u1_name,b.name u2_name,c.name u3_name
27 from user1 a,user2 b,user3 c,userStatus d
28 where d.user_id=a.id(+)
29 and d.user_id=b.id(+)
30 and d.user_id=c.id(+)
31 ;
USER_ID U1_NAM U2_NAM U3_NAM
---------- ------ ------ ------
5 user3a
6 user3b
4 user2b
3 user2a
1 user1a
2 user1b
6 rows selected.
select d.user_id,a.name u1_name,b.name u2_name,c.name u3_name
from user1 a,user2 b,user3 c,userStatus d
where d.user_id=a.id(+)
and d.user_id=b.id(+)
and d.user_id=c.id(+);