我相信Oracle函数FIRST_VALUE是基于这两个问题我需要使用的:
SQL – How to select a row having a column with max value
Oracle: Taking the record with the max date
我有3个表,代表与组织相关的人员.每个组织可能都有一个父组织,其中ORG.PARENT是ORG.ID的外键(因此表引用自身).一个人可能与多个组相关联.
人
ID NAME
----------
1 Bob
ORG
ID NAME PARENT
------------------------
1 A (null)
2 A-1 1
3 A-2 1
4 A-3 1
5 A-1-a 2
6 A-1-b 2
7 A-2-a 3
8 A-2-b 3
PERSON_TO_ORG
PERSON_ID ORG_ID
-----------------
1 1
1 3
我想列出一个人关联的组,所以我使用了这个查询:
SELECT NAME, ID, sys_connect_by_path(NAME, '/') AS path
FROM org
START WITH ID IN
(SELECT org_id FROM person_to_org WHERE person_id=1)
connect by prior org.ID = org.parent;
……这给了我:
NAME ID PATH
------------------
A-2 3 /A-2
A-2-a 8 /A-2/A-2-a
A-2-b 9 /A-2/A-2-b
A 1 /A
A-1 2 /A/A-1
A-1-a 5 /A/A-1/A-1-a
A-1-b 6 /A/A-1/A-1-b
A-2 3 /A/A-2
A-2-a 8 /A/A-2/A-2-a
A-2-b 9 /A/A-2/A-2-b
A-3 4 /A/A-3
注意A-2应该如何出现两次.但是,我不希望组出现两次.我希望一个组只出现在树中的最低级别,即最高级别的值.以下是我尝试使用FIRST_VALUE而没有运气的方法 – 我仍然让A-2(和其他人)出现两次:
SELECT id, name, path, first_value(lev) OVER
(
PARTITION BY ID,NAME, path ORDER BY lev DESC
) AS max_lev FROM
(SELECT NAME, ID, sys_connect_by_path(NAME, '/') AS path, LEVEL as lev
FROM org START WITH ID IN
(SELECT org_id FROM person_to_org WHERE person_id=1)
connect by prior org.ID = org.parent);
这看起来类似于Pro Oracle SQL中的FIRST_VALUE示例,但无论我如何调整参数,我似乎无法使其工作.
如何只返回给定组具有最高级别值(即树中最下方)的行?