我能想到的另一种方法是,更易于在其他关系型数据库管理系统上进行移植:http://www.sqlfiddle.com/#!4/da0a3/19
with parent(root_node, child_of, parent_id, depth) as
(
select id, id, parent_id, 1
from edition
union all
select p.root_node, e.id, e.parent_id, p.depth + 1
from edition e
join parent p on p.child_of = e.parent_id
)
select root_node, max(child_of)
from parent
where (root_node,depth) in
(select root_node,max(depth) from parent group by root_node)
group by root_node
order by root_node
输出:
| ROOT_NODE | MAX(CHILD_OF) |
-----------------------------
| 1 | 7 |
| 2 | 2 |
| 3 | 3 |
| 4 | 7 |
| 5 | 7 |
| 6 | 6 |
| 7 | 7 |
现在,我非常喜欢Oracle(还有
http://sqlfiddle.com),它非常简洁。现在我知道在KEEP DENSE_RANK中指定MIN和MAX的用途,而以前我没有看到任何实用性
明确指定KEEP DENSE_RANK中的MIN/MAX。现在我知道它有一个用途,如果深度上存在一些
ties,您可以使用MIN和MAX来查看谁是ties中的第一个和最后一个。例如
http://www.sqlfiddle.com/#!4/da0a3/24。
with parent(root_node, child_of, parent_id, depth) as
(
select id, id, parent_id, 1
from edition
union all
select p.root_node, e.id, e.parent_id, p.depth + 1
from edition e
join parent p on p.child_of = e.parent_id
)
select root_node,
min(child_of) keep(dense_rank last order by depth) as first_in_deepest,
max(child_of) keep(dense_rank last order by depth) as last_in_deepest
from parent
group by root_node;
| ROOT_NODE | FIRST_IN_DEEPEST | LAST_IN_DEEPEST |
--------------------------------------------------
| 1 | 6 | 7 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 6 | 7 |
| 5 | 6 | 7 |
| 6 | 6 | 6 |
| 7 | 7 | 7 |