我有以下两个表,第一个表中有分层数据:
公司:
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号
我错过了什么?
我使用的是oracle11,但是使用h2进行单元测试。因此,我的查询需要是cte查询,而不是oracleconnetby查询,因为h2只理解前者。
2条答案
按热度按时间oxcyiej71#
您需要选择
COMPANY_ID
作为两者COMPANY_ID
以及ROOT_ID
对于根公司:li9yvcax2#
您可以使用oracle的经典分层查询,包括
CONNECT_BY_ROOT
一起ROW_NUMBER()
用于在第一个查询(子查询)中筛选出根公司的分析函数companies
表,然后与users
表格:演示