获取分层表中子项列表的根元素

Question

我有以下 2 个表，第一个表中有分层数据：

公司：

COMPANY_ID    PARENT_COMPANY_ID
----------    -----------------
1             NULL
2             1
3             2
4             3
5             NULL
6             5

用户：

USER_ID       COMPANY_ID
-------       ----------
1             1
2             4
3             5
4             6

我想进行一个查询，在其根公司 ID 旁边输出用户 ID。我尝试使用以下公用表表达式 (CTE( 查询，但它无法输出直接隶属于根公司的用户：

查询：

WITH ROOT (COMPANY_ID, ROOT_ID) AS (
SELECT
CHILD.COMPANY_ID,
PARENT.COMPANY_ID
FROM COMPANIES CHILD
INNER JOIN COMPANIES PARENT
ON CHILD.PARENT_COMPANY_ID = PARENT.COMPANY_ID
WHERE
PARENT.PARENT_COMPANY_ID IS NULL
UNION ALL
SELECT
C.COMPANY_ID,
ROOT.ROOT_ID
FROM ROOT
INNER JOIN COMPANIES C
ON ROOT.COMPANY_ID = C.PARENT_COMPANY_ID
)
SELECT
U.USER_ID,
R.ROOT_ID
FROM USERS U
INNER JOIN ROOT R
ON U.COMPANY_ID = R.COMPANY_ID;

实际输出：

USER_ID    ROOT_COMPANY_ID
-------    ---------------
4             5
2             1

预期产出：

USER_ID    ROOT_COMPANY_ID
-------    ---------------
1             1
2             1
3             5
4             5

因此，我的查询缺少用户 ID 为 1 和 2 的用户及其各自的根公司 1 和 5。

我用我的例子创建了这个 sqlfiddle： http://sqlfiddle.com/#!4/36d33a/1

我在这里错过了什么？

我使用的是 Oracle 11，但使用 H2 进行单元测试。所以我的查询需要是一个 CTE 查询，而不是通过查询连接的 Oracle 连接，因为 H2 只理解前者。

Answer 1

您需要选择COMPANY_ID作为根公司的COMPANY_ID和ROOT_ID：

WITH ROOT(COMPANY_ID, ROOT_ID) AS (
SELECT COMPANY_ID, COMPANY_ID FROM COMPANIES WHERE PARENT_COMPANY_ID IS NULL
UNION ALL
SELECT C.COMPANY_ID, ROOT.ROOT_ID FROM COMPANIES C JOIN ROOT
ON C.PARENT_COMPANY_ID = ROOT.COMPANY_ID
) SELECT USER_ID, ROOT_ID FROM USERS JOIN ROOT
ON USERS.COMPANY_ID = ROOT.COMPANY_ID;

Answer 2

您可以使用Oracle 的经典分层查询(包括CONNECT_BY_ROOT(和ROW_NUMBER()分析函数一起过滤掉companies表的第一个查询(子查询(中的根公司，然后与users表连接：

WITH company AS
(
SELECT c.company_id, 
CONNECT_BY_ROOT NVL(c.parent_company_id,c.company_id) AS root_company_id,
ROW_NUMBER() OVER (PARTITION BY c.company_id ORDER BY level DESC) AS rn
FROM companies c
CONNECT BY PRIOR c.company_id = c.parent_company_id
)
SELECT u.user_id, c.root_company_id
FROM company c
JOIN users u
ON u.company_id = c.company_id
WHERE rn = 1 

演示