postgresql - 获取相关表postgres之间至少一个巧合的值

Question

我有 3 个表:

CREATE TABLE public.student (id serial NOT NULL PRIMARY KEY,"student" varchar(30) NOT NULL) WITH (OIDS = FALSE);

  CREATE TABLE public."class" (id serial NOT NULL PRIMARY KEY,"class" varchar(30) NOT NULL) WITH (OIDS = FALSE);



 CREATE TABLE public.student_class (student_id integer, class_id integer, /* Keys */ CONSTRAINT student_class_index01
    PRIMARY KEY (student_id, class_id)) WITH (OIDS = FALSE);

使用这些值:

INSERT INTO public.student_class ("student_id","class_id") VALUES(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,1),(2,2),(2,3),(3,4),(3,5),(3,6),(3,7),(3,8);

我现在想知道至少有一个类(class)与另一个类(class)重合的学生的姓名:

Student 1 has got at least one coincidence with 2 and 3. Student 2 has got at least one coincidence with 1. Student 3 has got at least one coincidence with 1.

我已经试过了，但不是学生做的。

SELECT distinct a.id,a.student from public.student a
LEFT OUTER JOIN public.student_class b ON b.student_id=a.id
LEFT OUTER JOIN public.class d ON d.id=b.class_id
WHERE class_id IN(select class_id from student_class where student_id not in(1))
AND a.id not in(1);

我觉得这比我想象的要容易。谢谢。

Answer 1

你怎么看这个:

with student_lst_classes as (
    select student_id,array_agg(class_id order by class_id) lst_classes
      from student_class  group by 1
 ) 
select st1.student,string_agg(st2.student,';' order by st2.student ASC) lst_student_coincident from 
 (student_lst_classes l1 join student st1 on (st1.id=l1.student_id)) 
 ,(student_lst_classes l2 join student st2 on (st2.id=l2.student_id))
where l1.student_id!=l2.student_id and l1.lst_classes && l2.lst_classes
group by 1 order by 1