Oracle11g中的级别排序
问题描述:
我正在尝试使用Oracle11g中的LEVEL
功能创建组织结构。Oracle11g中的级别排序
我有以下查询:
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名,则该树根据员工编号进行扩展。
谢谢!
答
您的数据的解释缺乏很多细节。无论如何,通过你试图执行的查询和你的例外输出,我想这个错误是你一次试图做很多事情:聚合和分层查询。
要获得所需的输出,connect by应该是查询评估的最后一步,但在查询之前无法评估聚合。
通过评估进行连接会按照您的预期顺序返回行,但分组子句witch在其评估中定义了隐式排序,将覆盖它,从而使行按分组键排序。
一般的SQL语句处理评估查询子句按以下顺序:
- FROM + WHERE + CONNECT BY
- GROUP BY
- HAVING
- 选择
- ORDER BY
所以你应该分开这两件事情粗略一个子查询,在执行分层查询阶段之前对计数聚合进行评估,而不对输出进行排序。
你可以试试这个查询:
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
没有,他的问题是,他需要订购他的结果。 –
查询的输出将按该顺序排列,因为在嵌套子元素中使用group by将保留分层条件的顺序,女巫是预期的顺序。希望清楚! –
...好的,事实证明'CONNECT BY'会做一些自己的排序。你的回答并没有说明原始陈述的写法覆盖了这个问题。我建议你调整你的答案来解决这个问题。 –