select e.name as ename,s.name as sname,c.name as cname,c2.name as c2name
from users e
left join users s on e.fid=s.fid and s.relation='38'
left join users c on e.fid=c.fid and c.relation='37'
left join users c2 on (e.fid=c2.fid and c2.relation='37' and c.peid<>c2.peid)
where e.relation = '00' MS SQL SERVER 2000中写如上语句,查询结果如下:员工1 null null null
员工2 员工2配偶 员工2子女1 null
员工3 null 员工3子女1 null
员工4 员工4配偶 员工4子女1 员工4子女4
如何转成oracle查询语句?
from users e
left join users s on e.fid=s.fid and s.relation='38'
left join users c on e.fid=c.fid and c.relation='37'
left join users c2 on (e.fid=c2.fid and c2.relation='37' and c.peid<>c2.peid)
where e.relation = '00' MS SQL SERVER 2000中写如上语句,查询结果如下:员工1 null null null
员工2 员工2配偶 员工2子女1 null
员工3 null 员工3子女1 null
员工4 员工4配偶 员工4子女1 员工4子女4
如何转成oracle查询语句?
--这是一个表的利用自关系联接的一个查询,改了下试试:select e.name as ename, s.name as sname, c.name as cname, c2.name as c2name
from users e, users s, users c, users c2
where e.relation = '00'
and e.fid = s.fid
and s.relation = '38'
and e.fid = c.fid
and c.relation = '37'
and e.fid = c2.fid
and c2.relation = '37'
and c.peid <> c2.peid;
select e.name as ename, s.name as sname, c.name as cname, c2.name as c2name
from users e, users s, users c, users c2
where e.relation = '00'
and e.fid = s.fid(+)
and s.relation = '38'
and e.fid = c.fid(+)
and c.relation = '37'
and e.fid = c2.fid(+)
and c2.relation = '37'
and c.peid <> c2.peid;