时间戳

首页 > 解决方案 > where 子句与 connect by And 创建查询的交互以获取层次结构中的下一个级别

sql - where 子句与 connect by And 创建查询的交互以获取层次结构中的下一个级别

问题描述

桌子：

create table temp_hierarchy_define (dept varchar2(25), parent_dept varchar2(25))
create table temp_employee (empid number(1), empname varchar2(50), dept varchar2(25), salary number(10))

数据

Select 'COMPANY' dept   , 'COMPANY' parent_dept  From Dual Union All
Select 'IT'        , 'COMPANY'  From Dual Union All
Select 'MARKET'    , 'COMPANY'  From Dual Union All
Select 'ITSEC'     , 'IT'       From Dual Union All
Select 'ITDBA'     , 'IT'       From Dual Union All
Select 'ITDBAORC'  , 'ITDBA'    From Dual Union All
Select 'ITDBASQL'  , 'ITDBA'    From Dual

select 1 empid, 'Rohan-ITDBASQL' empname   ,'ITDBASQL' dept ,10 salary from dual union all
select 2, 'Raj-ITDBAORC'     ,'ITDBAORC'  ,20 from dual union all
select 3, 'Roy-ITDBA'        ,'ITDBA'     ,30 from dual union all
select 4, 'Ray-MARKET'       ,'MARKET'    ,40 from dual union all
select 5, 'Roopal-IT'        ,'IT'        ,50 from dual union all
select 6, 'Ramesh-ITSEC'     ,'ITSEC'     ,60 from dual

要求

总结所有IT部门的工资：

类别 薪水

5,50

ITSEC,60

ITDBA,60

总结公司所有部门的工资：

类别 薪水

它,170

市场,40

总结所有ITDBA部门的工资：

类别 薪水

3,30

ITDBASQL,10

ITDBAORC,20

您会注意到我们正在尝试根据层次结构中的下一个级别进行汇总。如果任何 emp 已经是该级别的一部分，那么我们需要显示该员工。

试用查询：

Select Category,sum(salary) from (
Select 
NVL((Select dept.dept from temp_hierarchy_define dept
               Where dept.parent_dept = 'IT'
               And dept.dept != 'IT' 
               Start With dept.dept = emp.dept
               Connect by NOCYCLE dept.dept = Prior dept.parent_dept
               and prior dept.dept is not null),emp.empid) category,
emp.*
From temp_employee emp
Where emp.DEPT in
(Select dept.dept from temp_hierarchy_define dept
Start With dept.dept = 'IT' 
connect by nocycle prior dept.dept = dept.parent_dept) ) Group by Category

疑虑和疑问：

  1. 此查询是否适用于所有场景。或者有什么更好的方法吗？？
  2. where 条件如何与 connect by 交互。例如，在子查询中，我们使用 进行过滤parent_dept = 'IT'，但是在开始连接时，某些 emp 可能具有parent_dept = 'ITDBASQL'这也是 IT 的一部分。我很难理解工作流程。

感谢您的时间和帮助。

标签： sqloracleoracle11g

解决方案

或者有什么更好的方法吗？

这是一个等效查询，只需要对每个表进行一次表扫描。您将需要确定您的查询或这个查询是否对您的数据/索引/等更有效。

SQL小提琴

Oracle 11g R2 模式设置：

create table temp_hierarchy_define (
  dept varchar2(25), parent_dept varchar2(25));
create table temp_employee (
  empid number(1), empname varchar2(50), dept varchar2(25), salary number(10));

INSERT INTO temp_hierarchy_define( dept, parent_dept )
Select 'COMPANY'   , 'COMPANY'  From Dual Union All
Select 'IT'        , 'COMPANY'  From Dual Union All
Select 'MARKET'    , 'COMPANY'  From Dual Union All
Select 'ITSEC'     , 'IT'       From Dual Union All
Select 'ITDBA'     , 'IT'       From Dual Union All
Select 'ITDBAORC'  , 'ITDBA'    From Dual Union All
Select 'ITDBASQL'  , 'ITDBA'    From Dual;

INSERT INTO temp_employee( empid, empname, dept, salary )
select 1, 'Rohan-ITDBASQL'   ,'ITDBASQL'  ,10 from dual union all
select 2, 'Raj-ITDBAORC'     ,'ITDBAORC'  ,20 from dual union all
select 3, 'Roy-ITDBA'        ,'ITDBA'     ,30 from dual union all
select 4, 'Ray-MARKET'       ,'MARKET'    ,40 from dual union all
select 5, 'Roopal-IT'        ,'IT'        ,50 from dual union all
select 6, 'Ramesh-ITSEC'     ,'ITSEC'     ,60 from dual;

查询 1：

SELECT dept,
       SUM( salary )
FROM   (
  SELECT CASE
         WHEN lvl = 1 AND h.parent_dept = e.dept
         THEN CAST( e.empid AS VARCHAR2(25) )
         ELSE root_dept
         END AS dept,
         e.empid,
         e.salary
  FROM   ( SELECT CONNECT_BY_ROOT( dept ) AS root_dept,
                  h.*,
                  LEVEL AS lvl,
                  ROW_NUMBER() OVER ( PARTITION BY parent_dept ORDER BY ROWNUM ) AS rn
           FROM   temp_hierarchy_define h
           WHERE  parent_dept != dept
           START WITH h.parent_dept = 'IT'
           CONNECT BY NOCYCLE PRIOR h.dept = h.parent_dept
         ) h
         LEFT OUTER JOIN
         temp_employee e
         ON (  h.dept = e.dept
            OR ( h.parent_dept = e.dept AND h.lvl = 1 AND h.rn = 1)    
            )
)
GROUP BY dept

结果：

|  DEPT | SUM(SALARY) |
|-------|-------------|
| ITDBA |          60 |
|     5 |          50 |
| ITSEC |          60 |

您可以单独运行查询以了解它们在做什么：

SELECT CONNECT_BY_ROOT( dept ) AS root_dept,
       h.*,
       LEVEL AS lvl,
       ROW_NUMBER() OVER ( PARTITION BY parent_dept ORDER BY ROWNUM ) AS rn
FROM   temp_hierarchy_define h
WHERE  parent_dept != dept
START WITH h.parent_dept = 'IT'
CONNECT BY NOCYCLE PRIOR h.dept = h.parent_dept

只需列出层次结构中的所有行，并用于CONNECT_BY_ROOT获取层次结构分支根部的部门。LEVEL并ROW_NUMBER()用于查找层次结构顶部的第一行。

推荐阅读