sql - 递归sql子集查询，使用connect by

Question

我有两个看起来有点像这样的表

AGR_TERR_DEF_TERRS
  ID
  DEF_ID
  TERRITORY_ID (foreign key links to TERRITORIES.ID)

TERRITORIES
  ID
  NAME
  PARENT_ID
(parent_id and id are recursive)

给定两个 DEF_ID，我需要一个函数来检查一个的领土是否是另一个的完整子集。我一直在玩 CONNECT BY 和 INTERSECT，但写得一团糟而不是一个有用的函数。

我希望有一个(相对)简单的 SQL 查询可以工作。

Answer 1

基于@Tony Andrews 的回答，当 def_id_1 隐含的领土是 def_id_2 的子集时，这将产生零行，

select id from territories start with id in
  (select territory_id from agr_terr_def_terrs where def_id = :def_id_1)
  connect by parent_id = prior id
minus
select id from territories start with id in
  (select territory_id from agr_terr_def_terrs where def_id = :def_id_2)
  connect by parent_id = prior id