sql - 带有视图和连接的 Oracle 分层查询

Question

我有一个这样的表设置：

create table DEP
(
  depid   NUMBER,
  name    VARCHAR2(200),
  manager NUMBER
);


create table EMP
(
  empid   NUMBER,
  depid   NUMBER,
  name    VARCHAR2(200),
  role    VARCHAR2(200),
  salary  NUMBER,
  manager NUMBER
);

insert into dep (DEPID, NAME, MANAGER)
values (1, 'IT', 2);

insert into dep (DEPID, NAME, MANAGER)
values (2, 'ACCOUNTING', 4);

insert into dep (DEPID, NAME, MANAGER)
values (3, 'MARKETING', 5);


insert into emp (EMPID, DEPID, NAME, ROLE, SALARY, MANAGER)
values (1, 1, 'KEVIN', 'PROGRAMMER', 20000, 2);

insert into emp (EMPID, DEPID, NAME, ROLE, SALARY, MANAGER)
values (2, 1, 'LOUIS', 'ANALYST', 30000, null);

insert into emp (EMPID, DEPID, NAME, ROLE, SALARY, MANAGER)
values (3, 2, 'RACHEL', 'CASHER', 15000, 4);

insert into emp (EMPID, DEPID, NAME, ROLE, SALARY, MANAGER)
values (4, 2, 'JOHN', 'ECONOMIST', 50000, null);

insert into emp (EMPID, DEPID, NAME, ROLE, SALARY, MANAGER)
values (5, 3, 'ERNEST', 'WRITER', 22000, null);

insert into emp (EMPID, DEPID, NAME, ROLE, SALARY, MANAGER)
values (6, 3, 'JACK', 'COMMUNITY MANAGER', 18000, 5);

然后我创建这两个等效视图

create or replace view depemp1 as 
with v as (
 select emp.empid, dep.depid, dep.name depname, emp.name, emp.role, emp.salary,emp.manager
 from emp, dep
 where emp.depid = dep.depid
)
select v1.empid  v1_empid, v1.depid v1_depid, v1.depname v1_depname, v1.name v1_name, v1.role v1_role, v1.salary v1_salary,v1.manager v1_manager
,v2.empid  v2_empid, v2.depid v2_depid, v2.depname v2_depname, v2.name v2_name, v2.role v2_role, v2.salary v2_salary,v2.manager v2_manager
from v v1 ,v v2
where v1.empid = v2.empid


create or replace view depemp2 as
select emp1.empid  v1_empid, dep1.depid v1_depid, dep1.name v1_depname, emp1.name v1_name, emp1.role v1_role, emp1.salary v1_salary,emp1.manager v1_manager
,emp2.empid v2_empid, dep2.depid v2_depid, dep2.name v2_depname, emp2.name v2_name, emp2.role v2_role, emp2.salary v2_salary,emp2.manager v2_manager
from emp emp1, emp emp2, dep dep1, dep dep2
where emp1.depid = dep1.depid and emp2.depid = dep2.depid
and emp2.empid = emp1.empid

如果我从中选择，我可以检查它。

select * from depemp1;

select * from depemp2;

现在让我们考虑下一个对 depemp1 的查询：

select
 level, 
 depemp1.*
  from depemp1
connect by prior v1_empid = v1_manager
 start with v1_manager is null

上面的查询没有结果。然而，对 depemp2 的相同查询（提供与 depemp1 相同的行和数据）提供 6 行。

select
 level, 
 depemp2.*
  from depemp2
connect by prior v1_empid = v1_manager
 start with v1_manager is null

我正在使用 Oracle 10g，但我不明白为什么会出现这种行为。我认为这个分层查询适用于 depemp1 和 depemp2 的相同数据输出，并且应该给出相同的结果。

标签： sqloracleoracle10g