Oracle SQL查询层次结构计数
Oracle SQL查询层次结构计数
提问于 2018-06-09 03:24:54
我有两个表1-部门(一些部门是其他部门的一部分)。
2-员工，在部门中工作
WITH `d1` AS (
SELECT 1 ID, 'dep1' NAME, null Parent_id UNION ALL
SELECT 2, 'dep2', null UNION ALL
SELECT 3, 'dep21', 2 UNION ALL
SELECT 4,'dep22', 2
)
WITH `d2` AS (
SELECT 1 ID, 'Name1' NAME, 3 DEP_id UNION ALL
SELECT 2, 'Name2', 4 UNION ALL
SELECT 3, 'Name3', 1 UNION ALL
SELECT 4, 'Name4', 2 UNION ALL我需要找出每个部门的员工人数，包括家长。我想我必须使用"connect by“功能，但我不知道如何使用它。结果是：
ID Qty
1 1
2 3
3 1
4 1回答 1
Stack Overflow用户
发布于 2018-06-09 04:21:17
正如您所怀疑的那样，CONNECT BY是必需的。诀窍是省略START WITH子句，这样每个部门都被视为“根”。然后，我们可以计算每个“根”的雇员人数--即每个部门及其所有子部门的雇员人数。
这里又是你的例子。我还在您的部门结构中添加了一个额外的级别，作为更高级的测试用例。
WITH dept ( id, name, parent_id) AS (
SELECT 1, 'dep1', null FROM DUAL UNION ALL
SELECT 2, 'dep2', null FROM DUAL UNION ALL
SELECT 3, 'dep21', 2 FROM DUAL UNION ALL
SELECT 4,'dep22', 2 FROM DUAL UNION ALL
SELECT 5, 'dep211', 3 FROM DUAL
),
emp (id, name, dep_id) AS (
SELECT 1, 'Name1', 3 FROM DUAL UNION ALL
SELECT 2, 'Name2', 4 FROM DUAL UNION ALL
SELECT 3, 'Name3', 1 FROM DUAL UNION ALL
SELECT 4, 'Name4', 2 FROM DUAL UNION ALL
SELECT 5, 'Name5', 5 FROM DUAL
),
intermediate as (
select connect_by_root d.name deptname, level lvl, e.id empid, e.name empname
from dept d left join emp e on e.dep_id = d.id
-- Unfortunately, connecting this way, we cannot also determine the "level" of each
-- department. To do that, we would need the CONNECT BY to be reversed, i.e.,:
-- connect by prior d.parent_id = d.id
connect by d.parent_id = prior d.id
-- No "START WITH" clause
)
SELECT deptname,
count(empid) empcount,
listagg(empname,', ') within group ( order by empname) emplist
FROM intermediate
GROUP BY deptname
ORDER BY deptname;EMPLIST +----------+----------+----------------------------+ | DEPTNAME | EMPCOUNT |EMPLIST|+----------+----------+--+ | dep1 |1| Name3 || dep2 |4| Name1，Name2，Name4，Name5 || dep21 |2| Name1，Name5 || dep211 |1| Name5 || dep22 |1| Name2 |
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50767225
复制相关文章
相似问题