mysql中集合相等_SQL问题：集合相等

假设有一张成绩表(score)&＃xff1a;字段如下&＃xff1a; sid&＃xff1a;学生学号&＃xff0c; cid&＃xff1a;课程编号&＃xff0c; score&＃xff1a;成绩 求所修课程完全相同的同学的学号。这个问题其实关键在于求课程集合相等的问题。 可以使用如下sql即可求出两个所修课程完全相同的同学的学号&＃xff1a; select s1.sid,s2.sid fro

假设有一张成绩表(score)&＃xff1a;字段如下&＃xff1a; sid&＃xff1a;学生学号&＃xff0c; cid&＃xff1a;课程编号&＃xff0c; score&＃xff1a;成绩 求所修课程完全相同的同学的学号。这个问题其实关键在于求课程集合相等的问题。 可以使用如下sql即可求出两个所修课程完全相同的同学的学号&＃xff1a;

select s1.sid,s2.sid from score s1 INNER JOIN score s2 ON

(s1.cid&＃61;s2.cid and s1.sid

HAVING count(*) &＃61; (select count(*) from score where s1.sid&＃61;sid)

AND

count(*) &＃61; (select count(*) from score where s2.sid&＃61;sid);

集合相等的问题可以衍生出如求与学号002同学所修课程完全相同的其它同学的学号和姓名&＃xff1a;

select st.sid,st.sname from student st where st.sid in ( select sc.sc_sid from ( select s1.sid,s2.sid as sc_sid from score s1 INNER JOIN score s2 ON

(s1.cid&＃61;s2.cid and s1.sid <> s2.sid and s1.sid&＃61;&＃39;002&＃39;) GROUP BY s1.sid,s2.sid

HAVING count(*) &＃61; (select count(*) from score where sid&＃61;s1.sid)

and count(*) &＃61; (select count(*) from score where sid&＃61;s2.sid) ) sc );

本文原创发布php中文网&＃xff0c;转载请注明出处&＃xff0c;感谢您的尊重&＃xff01;