对单个表进行递归选择或联接

Question

我有一个表，它有一列用于name、grp_ip(组id(和pgrp_id(父组id(。

我的出发点是选择任何父组id为1083:的条目

select grp_id, pgrp_id, name from CM_GROUP where pgrp_id = 1083

这给我看了下面的

新南威尔士州

grp_id	pgrp_id	名称
1084	1083	ACT
1086	1083	VIC
1087	1083	昆士兰州
1088	1083	SA
1089	1083	华盛顿州
1090	1083	TAS
1091	1083	NT

这似乎是三个级别：状态、站点和系统。我想知道为什么这是一个层次表，而不是三个单独的表。您的数据库允许一个国家成为一个国家的一部分，允许一个站点成为一个系统的一部分。

我会更改数据模型。但是，我们可以在查询中将一个表视为单独的表。

with states as
(
select grp_id as state_id, name as state_name
from mytable
where pgrp_id = 1083
)
, sites as
(
(
select grp_id as site_id, pgrp_id as state_id, name as site_name
from mytable
where pgrp_id in (select state_id from states)
)
, systems as
(
(
select grp_id as system_id, pgrp_id as site_id, name as system_name
from mytable
where pgrp_id in (select site_id from sites)
)
select
sy.system_id, sy.system_name, si.site_id, si.site_name, st.state_id, st.state_name
from states st
join sites si on si.state_id = st.state_id
join systems sy on sy.site_id = si.site_id
order by st.state_id, si.site_id, sy.system_id;

同样的东西要短得多，更容易出错，可读性可能稍差，因为ID最终会得到它们的名字：

select
sy.prg_id as system_id, sy.name as system_name,
si.prg_id as site_id, si.name as site_name,
st.prg_id as state_id, st.name as state_name
from mytable st
join mytable si on si.pgrp_id = st.prg_id
join mytable sy on sy.pgrp_id = si.prg_id
where st.pgrp_id = 1083
order by st.prg_id, si.prg_id, sy.prg_id;