求个sql语句

[fdy1984]

表A
id name
-------------
1 tom
2
3 nick
4 lucy


表B
id class
-------------
2 a1
2 c3
4 d2

表C
id sex
-------------
1 F
2 M
4 F
4 F

想要的结果
id name class sex
1 tom F
2 cat a1 M
2 cat c3
3 nick
4 lucy d2 F
4 F

就是三个表的连接
用oracle

[fuyou001]

select a.id, a.name, b.class, c.sex
  from a
  left outer join b on  a.id = b.id
  left outer  join c on a.id = c.id

[Andy__Huang]

select a.id,a.name,b.class,c.sex
from a,b,c
where a.id=b.id and a.id=c.id

[lhj821130]

感觉一条简单的SQL不可能实现

[happy84911]

SELECT A.*,B.CLASS,C.SEX FROM A,B,C WHERE
A.ID=B.ID(+)
AND A.ID=C.ID(+)

[watson110]

union

[fdy1984]

都不行。楼上的我都用过，都不能实现
happy84911的这位朋友的最接近
可是还是会出现迪卡尔连接

[wojiaochenglong]

用一条语句应该是不可以了，楼主可以试试利用双层循环判断一下

[wojiaochenglong]

用一条语句应该是不可以了，楼主可以试试利用双层循环判断一下

[cosio]

select 
id,decode(mm,1,name,'')name,decode(mm,1,class,'')class,(case when ll<=mm then sex else '' end) sex
from
(
select id,name,class,sex,nn,
row_number() over(partition by id,nn order by id) ll,mm
from 
(
select a.id id,name,class,sex,nn,row_number() over(partition by name,class order by id) mm from a left outer join b on a.id = b.id  
full join (select c.*,row_number() over(partition by c.id order by c.id)nn from c )c on a.id=c.id order by a.id)
)


--result:

1	tom		F
2	cat	a1	M
2	cat	c3	
3	nick		
4	lucy	d2	F
4			F