select a.id,a.name,a.parent_id,p.name as parent_name,c.id as child_id,c.name as child_name
from res a
left join res p on p.id=a.parent_id
left join res c on c.parent_id=a.id
from res a
left join res p on p.id=a.parent_id
left join res c on c.parent_id=a.id
如果我想在结果中加多一个字段 id。应该怎么加?
from res r1,res r2,res r3
where r1.parent_id=r2.id and r3.parent_id=r1.id有必要就左连接select r1.id,r1.parent_id,r1.type,r1.name,r2.id,r2.name,r3.id,r3.name
from res r1,res r2,res r3
where r1.parent_id=r2.id(+) and r3.parent_id(+)=r1.id
case b.type when NULL then
a.id
else
case c.type when NULL then
b.id
else
c.id
end
end as resID,
a.id,
a.name,
b.id,
b.name,
c.id,
c.name
FROM
res a
LEFT JOIN
res b
ON b.parent_id = a.id
LEFT JOIN
res c
ON c.parent_id = b.id我这样执行,但得当所有的id都有值的情况下它才会显示resID的值为最后一个id(即c.id),其它的resID都显示为空
start with id = xxx
connect by prior id = parent_id ;