获取层次表中子级列表的根元素

643ylb08  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(330)

我有以下两个表,第一个表中有分层数据:
公司:

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只理解前者。

oxcyiej7

oxcyiej71#

您需要选择 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;
li9yvcax

li9yvcax2#

您可以使用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

演示

相关问题