sql - 在带有嵌套表列 sql oracle 的表中查找不相交的集合

Question

这些是示例数据:

CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);

CREATE TABLE department (
courses  CourseList)
NESTED TABLE courses STORE AS courses_tab;

INSERT INTO department (courses)VALUES (CourseList('1','2','3'));
INSERT INTO department (courses)VALUES (CourseList('4','5','7'));
INSERT INTO department (courses)VALUES (CourseList('1','2'));

commit;

 select d1.courses
from department d1
where not exists (select 1 from department d2 
  where d1.courses<> d2.courses and d1.courses submultiset of d2.courses);

commit;

Result:

CourseList(1,2,3)
CourseList(4,5,7)

该查询返回正确的数据 CourseList，它不是表的任何其他 CourseList 的子集。

关于如何在没有子查询的情况下做到这一点的一些想法，我认为可以使用同一个表的 join 来完成，但我不知道该怎么做。

谢谢。

Answer 1

我更喜欢你的子查询，我自己。但另一种选择是:

select d.courses
from department d
MINUS
select d2.courses
from department d2, department d1
where d1.courses<> d2.courses
and d1.courses submultiset of d2.courses;