sql - Oracle 中的分层查询 - 失控？

Question

我在过去可以工作的 Oracle 10 SQL 中有一个分层查询。但是，我删除了它所基于的物化 View ，现在我无法让它正确显示，甚至完全离开了那个 View 。

原始查询如下所示:

select oh.name, oh.description
  , sys_connect_by_path(groupname, ':') "Groups"
  , (select count(*) 
      from ml.lastobsmv 
      where lastobsmv.hdid = oh.hdid) as obscount
from ml.obshead oh
  join ml.hiergrps hg on oh.groupid = hg.groupid
    connect by prior hg.groupid = hg.parentid

我认为它仍然有效，但没有 lastobsmv View ，我无法对其进行测试。

如果我把它修剪成

select oh.name, oh.description
from  ml.obshead oh
  join ml.hiergrps hg on oh.groupid = hg.groupid

它仍然有效，返回 41K 记录。但是，当我使用 connect by 子句时，它会失控，返回数百万条记录(我通常必须取消它才能获得准确的计数)。

select oh.name, oh.description
  , sys_connect_by_path(groupname, ':') "Groups"
from ml.obshead oh
  join ml.hiergrps hg on oh.groupid = hg.groupid
    connect by prior hg.groupid = hg.parentid

我是否在这里遗漏了一些非常明显的东西，或者我误解了它应该如何工作？谢谢。

---

瓦迪姆，

它应该返回一个观察术语列表以及它们所在的组。例如，

Obshead:

# CYCLE DAYS, number of days in menstrual cycle, 100

HierGrps:

100, 50, Gynecology
50, 10, Tx
10, 0, Basic

应该产生

# CYCLE DAYS, number of days in menstrual cycle, :Basic:Tx:Gynecology

(最终还有这个 obs 术语被使用的次数，但我稍后会担心)。

Answer 1

select
    oh.name,
    oh.description,
    hg."Groups"
from
    obshead oh
    join ( select
               groupid,
               sys_connect_by_path( groupname, ':' ) "Groups"
           from
               hiergrps
           start with
               parentid = 0
           connect by
               prior groupid = parentid
         ) hg
      on oh.groupid = hg.groupid

这是我用来测试查询的示例数据:

create table obshead
( name varchar2(30)
, description varchar2(30)
, groupid number(3)
);
insert into obshead ( name, description, groupid )
     select 'Name One', 'Description One', 100 from dual union all
     select 'Name Two', 'Description Two', 200 from dual
;

create table hiergrps
( groupid number(3)
, parentid number(3)
, groupname varchar2(30)
);
insert into hiergrps ( groupid, parentid, groupname )
     select 100, 50, 'Gynecology' from dual union all
     select  50, 10, 'Tx'         from dual union all
     select  10,  0, 'Basic'      from dual
;