sql - 在 Oracle11g 中按级别排序

Question

我正在尝试使用 Oracle11g 中的 LEVEL 功能创建组织结构。

我有以下查询:

SELECT
  level,
  lpad(' ', 3 * (level - 1)) || em.empno || ' : ' || em.fname || ' ' || em.lname "Employee",
  em.position                                                                    "Position",
  outno || ': ' || ou.street || ' ' || ou.city || ' ' || ou.zipcode              "Outlet",
  count(DISTINCT fa.reportnum)                                                   "# of Fault Reports"
FROM employee em
  INNER JOIN outlet ou USING (outno)
  LEFT JOIN faultreport fa ON fa.empno = em.empno AND fa.datechecked > (SYSDATE - 91)
START WITH em.empno = 30012
CONNECT BY PRIOR em.empno = em.supervisorno
GROUP BY level,
  em.empno, em.fname, em.lname, em.position,
  outno, ou.street, ou.city, ou.zipcode;

这给了我以下输出:

LEVEL Employee                          Position             Outlet                         # of Fault Reports
----- --------------------------------- -------------------- ------------------------------ ------------------
    1 30012 : Moreno Bale               Owner                119: Forrest Adelaide 5005                      0
    2    45611 : Annah Marlek           Area_Manager         112: Beauford Port Cambia 5001                  0
    2    48900 : Geoff Hanna            Area_Manager         118: Icecream Iyanee 5008                       0
    3       23490 : Abel Cole           Admin_Assistant      116: Huntington Banshee 5007                    0
    3       31459 : Chris Boss          Admin_Assistant      119: Forrest Adelaide 5005                      0
    3       60021 : Beau Rueford        Admin_Assistant      111: Junlee Caprice 5009                        0
    3       67823 : Jess Fred           Head_Mechanic        114: Elephant Ocupus 5004                       0
    4          55601 : Kabil Malla      Mechanic             115: Dundee Eeyrie 5003                         1
    4          55602 : Harry Potter     Mechanic             111: Junlee Caprice 5009                        5
    4          60020 : Maria Marbosa    Sales_Rep            113: Cathany Zeus 5002                          0
    4          77689 : Javier Martin    Sales_Rep            112: Beauford Port Cambia 5001                  0

11 rows selected.

但是，当我看到表格时，这是确切的关系:

Employee    Manager
    30012   -
    48900   30012
    45611   30012
    23490   45611
    31459   48900
    67823   48900
    55602   67823
    55601   67823
    60021   48900
    77689   60021
    60020   60021

我如何在 Oracle 中实现它，以便获得以下输出:

LEVEL Employee                          Position             Outlet                         # of Fault Reports
----- --------------------------------- -------------------- ------------------------------ ------------------
    1 30012 : Moreno Bale               Owner                119: Forrest Adelaide 5005                      0
    2    45611 : Annah Marlek           Area_Manager         112: Beauford Port Cambia 5001                  0
    3       23490 : Abel Cole           Admin_Assistant      116: Huntington Banshee 5007                    0
    2    48900 : Geoff Hanna            Area_Manager         118: Icecream Iyanee 5008                       0
    3       31459 : Chris Boss          Admin_Assistant      119: Forrest Adelaide 5005                      0
    3       60021 : Beau Rueford        Admin_Assistant      111: Junlee Caprice 5009                        0
    4          60020 : Maria Marbosa    Sales_Rep            113: Cathany Zeus 5002                          0
    4          77689 : Javier Martin    Sales_Rep            112: Beauford Port Cambia 5001                  0
    3       67823 : Jess Fred           Head_Mechanic        114: Elephant Ocupus 5004                       0
    4          55601 : Kabil Malla      Mechanic             115: Dundee Eeyrie 5003                         1
    4          55602 : Harry Potter     Mechanic             111: Junlee Caprice 5009                        5

11 rows selected.

请注意，如果经理管理的员工超过 1 名，则树会根据员工编号展开。

谢谢!

Answer 1

您对数据的解释缺乏很多细节。无论如何，根据您尝试执行的查询和您的异常输出，我认为错误是您试图同时做很多事情:同时进行聚合和分层查询。

要获得所需的输出，连接依据应该是查询评估的最后一步，但在查询中不能在它之前评估聚合。

评估连接按您预期的顺序返回行，但分组子句在其评估中定义了隐式排序，覆盖它给出按分组键排序的行。

通常 SQL 语句处理按以下顺序评估查询子句:

1. 来自 + 地点 + 连接方式
2. 分组依据
3. 拥有
4. 选择
5. 订购方式

因此，在执行分层查询阶段而不对输出进行排序之前，您应该通过评估计数聚合的子查询将这两件事分开。

你可以试试这个查询:

SELECT LEVEL, LPAD(' ', 3*(LEVEL - 1)) || empno || ' : ' || EM.fname || ' ' || EM.lname "Employee", 
    EM.position "Position", outno || ': ' || OU.street || ' ' || OU.city || ' ' || OU.zipcode "Outlet", 
    dist_reportnums "# of Fault Reports" 
FROM employee EM
    JOIN outlet OU using (outno) 
    LEFT JOIN (
            selec empno, COUNT(DISTINCT FA.reportnum) as dist_reportnums
            from faultreport FA
            where FA.datechecked > (SYSDATE - 91)
            GROUP BY empno
        ) using (empno)
START WITH empno = 30012
CONNECT BY PRIOR empno = EM.supervisorno