如何从同一个表中选择唯一的元组

I want to find all pairs of diffrent students that are enrolled in two or more sections together from

我想找到所有参加过两个或更多部分的不同学生

enroll(sid*, grade, dname*, cno*, sectno*)

where each section is uniquely identified by (dname*, cno*, sectno*)

其中每个部分由(dname *,cno *,sectno *)唯一标识

3 个解决方案

#1

You can self join on the columns that identify a section and specify e1.sid < e2.sid to fetch each pair of students only once.

您可以在标识节的列上自行加入,并指定e1.sid 以仅获取每对学生一次。

select e1.sid, e2.sid from enroll e1
join enroll e2 on e1.dname = e2.dname
and e1.cno = e2.cno
and e1.secno = e2.sectno
where e1.sid < e2.sid
group by e1.sid, e2.sid
having count(*) > 1

#2

/*
with enroll as (
select 1 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 1 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 2 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 3 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 1 sid, 'dname' dname, 2 cno, 2 secno from dual union all
select 2 sid, 'dname' dname, 2 cno, 2 secno from dual
)
*/
select e1.sid, e2.sid from enroll e1
join enroll e2 on e1.dname = e2.dname
and e1.cno = e2.cno
and e1.secno = e2.secno
where e1.sid < e2.sid
group by e1.sid, e2.sid
having count(*) >= 2;

(But this query won't work correctly if one sid enrolled on the same section more then once)

(但是如果一个sid在同一部分上注册多次一次,则此查询将无法正常工作)

#3

with enroll as (
select 1 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 1 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 2 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 3 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 3 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 1 sid, 'dname' dname, 2 cno, 2 secno from dual union all
select 2 sid, 'dname' dname, 2 cno, 2 secno from dual
)
select rn,sid from (
select row_number() over (partition by sid order by dname) as rn ,sid
from enroll ) 
where rn>1

use row_number()

this query work correctly if one sid enrolled on the same section more then once

如果一个sid在同一部分上注册多次,则此查询正常工作

distinct can be used if required

如果需要,可以使用distinct

with enroll as (
select 1 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 1 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 2 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 3 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 3 sid, 'dname' dname, 1 cno, 1 secno from dual union all
select 1 sid, 'dname' dname, 2 cno, 2 secno from dual union all
select 4 sid, 'dname' dname, 2 cno, 2 secno from dual union all
select 2 sid, 'dname' dname, 2 cno, 2 secno from dual
)
select distinct  sid from (
select row_number() over (partition by sid order by dname) as rn ,sid
from enroll ) 
where rn>1

Output

>        SID
> ----------
>          1
>          2
>          3

#1