Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
433 views
in Technique[技术] by (71.8m points)

sql - how to select unique tuples from same table

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*)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...