使用CONNECT BY和多个表的Oracle 11g CREATE VIEW [英] Oracle 11g CREATE VIEW using CONNECT BY and multiple tables
问题描述
使用Oracle 11G(而不是R2)数据库，我们需要创建报告，以显示个人的报告结构属于哪个领导力委员会.
Using Oracle 11G (not R2) database, we need to create reports that show which Leadership Committe an individual's reporting structure belongs to.
总体而言，我们通过在table_members表中找到员工ID来确定个人在董事会中的存在.
At a high level, we determine an individual's presence on the board by locating the employee ID in table board_members.
board_members表具有可用于访问board_positions的职位ID，然后我们可以从中确定该职位是否在领导委员会中. (下面的示例.)
The board_members table has a position ID that can be used to access board_positions, and from there we can determine if the position is on the leadership committee. (Samples below.)
对于在领导委员会任职的任何员工，其自己的ID都将代表BOARD_LEAD.
For any employee who is on the leadership committee, his own ID will represent BOARD_LEAD.
对于其他任何员工，都将重复使用report_to值，直到确定了领导委员会成员为止，该人员的ID将为BOARD_LEAD.
For any other employee, the report_to value is recursed until a Leadership Committee member is identified, and that person's ID will be BOARD_LEAD.
我们的高层职员的report_to等于他们自己的empl_id，而不是更常见的NULL.
Our top-level staff have report_to equal their own empl_id, rather than the more-common NULL.
为了希望证明这一点，我在下面设置了示例表，示例数据和示例所需的输出.
To hopefully demonstrate this, I have set up sample tables, sample data, and sample desired output below.
我正在尝试了解如何创建一个VIEW，该VIEW将为全时等效和其他报告需求提供此类信息.我确信将涉及CONNECT BY，但是我发现Oracle文档令人困惑，并且没有找到包含多个此类表的示例. (我担心这种缺乏榜样的理由很充分.)
I am trying to understand how to create a VIEW that will provide such information for Full Time Equivalence and other reporting needs. I am convinced that CONNECT BY will be involved, but I find the Oracle documentation confusing and I have found no examples that include multiple tables such as this. (I fear there is good reason for this lack of example.)
是否甚至有可能在Oracle 11g(不是R2)上编写这样的视图，而不是必须随每次位置变化而更新的中间表?
Is it even possible to write such a view at Oracle 11g (not R2) rather than an intermediate table that must be updated with each position change?
Create table board_positions /* If board_position = 'LDRSHPCOMM' this is a top position */
(member_id varchar(6),board_position varchar(18));
Create table board_members
(empl_id varchar(6), member_id varchar(6));
Create table emp
(empl_id varchar(6),ename varchar(32),report_to varchar(6));
Insert into board_positions values('CEO','LDRSHPCOMM');
Insert into board_positions Values('COO','LDRSHPCOMM');
Insert into board_positions Values('CFO','LDRSHPCOMM');
Insert into board_positions Values('CIO','LDRSHPCOMM');
Insert into board_positions values('WANABE','NEWBIE');
Insert into emp ('TOPDOG','Big Guy','TOPDOG');
Insert into emp ('WALLET','Money Bags','TOPDOG');
Insert into emp ('OPSGUY','Meikut Work','TOPDOG');
Insert into emp ('INFGUY','Comp U Turk','TOPDOG');
Insert into emp ('HITECH','Number 2','INFGUY');
Insert into emp ('LOTECH','Number 3','HITECH');
Insert into emp ('PROGMR','Nameless Blameless','LOTECH');
insert into emp ('FLUNKY','Ida Dunnit','PROGMR');
Insert into board_members ('TOPDOG','CEO');
Insert into board_members ('WALLET','CFO');
Insert into board_members ('OPSGUY','COO');
Insert into board_members ('INFGUY','CIO');
Insert into board_members ('HITECH','WANABE'); /* Board position not on the leadership committee */
使用类似的东西
CREATE VIEW LEADER_VIEW AS
WITH T1 AS (SELECT e.empl_id, (something) as board_lead
, (something) as board_lead_pos
FROM emp e
LEFT OUTER JOIN board_members bm
ON bm.empl_id = e.empl_id
LEFT OUTER JOIN board_positions bp
on bp.member_id = bm.member_id
...
CONNECT BY PRIOR empl_id = report_to
START WITH empl_id = report_to
)
SELECT * FROM T1
(但是我知道还有很多！)
(But I know there's much more to it than this!)
所需的输出示例. .
Desired output example . . .
TOPDOG TOPDOG CEO (Because self is on LDRSHPCOMM)
WALLET WALLET CFO (Because self is on LDRSHPCOMM)
OPSGUY OPSGUY COO (Because self is on LDRSHPCOMM)
INFGUY INFGUY CIO (Because self is on LDRSHPCOMM)
HITECH INFGUY CIO (Because REPORTTO is on LDRSHPCOMM)
LOTECH INFGUY CIO (Because REPORTTO->REPORTTO is on LDRSHPCOMM)
PROGMR INFGUY CIO (REPORTTO->REPORTTO->REPORTTO is on LDRSHPCOMM)
FLUNKY INFGUY CIO (You know by now.)
推荐答案
您可以执行以下操作:
SQL> SELECT *
2 FROM (SELECT empl_id, ename, report_to,
3 member_id, board_position,
4 MAX(lvl) over(PARTITION BY empl_id) maxlvl, lvl
5 FROM (SELECT connect_by_root(e.empl_id) empl_id,
6 connect_by_root(e.ename) ename,
7 bm.empl_id report_to,
8 LEVEL lvl, bp.*
9 FROM emp e
10 LEFT JOIN board_members bm
11 ON e.empl_id = bm.empl_id
12 LEFT JOIN board_positions bp
13 ON bm.member_id = bp.member_id
14 CONNECT BY NOCYCLE e.empl_id = PRIOR e.report_to
15 AND (PRIOR bp.board_position IS NULL
16 OR PRIOR bp.board_position != 'LDRSHPCOMM')))
17 WHERE lvl = maxlvl;
EMPL_ID ENAME REPORT_TO MEMBER_ID BOARD_POSITION
------- -------------------------------- --------- --------- ------------------
FLUNKY Ida Dunnit INFGUY CIO LDRSHPCOMM
HITECH Number 2 INFGUY CIO LDRSHPCOMM
INFGUY Comp U Turk INFGUY CIO LDRSHPCOMM
LOTECH Number 3 INFGUY CIO LDRSHPCOMM
OPSGUY Meikut Work OPSGUY COO LDRSHPCOMM
PROGMR Nameless Blameless INFGUY CIO LDRSHPCOMM
TOPDOG Big Guy TOPDOG CEO LDRSHPCOMM
WALLET Money Bags WALLET CFO LDRSHPCOMM
我没有START WITH子句，因为我想为所有员工启动分层查询.对于每位员工，我都会遍历分层数据，直到找到领导委员会中的董事会成员的经理(CONNECT BY子句).
I have no START WITH clause because I want to start the hierarchical query for all employees. For each employee I walk through the hierarchical data until I find a manager that is a board member on the leadership committee (CONNECT BY clause).
外部查询过滤相关行.
这篇关于使用CONNECT BY和多个表的Oracle 11g CREATE VIEW的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
