student
+------+-------+--------------------------------------+
| Sid | sName | content |
+------+-------+--------------------------------------+
| 1 | Tim | ... |
| 2 | boyo | ... |
| 3 | Carl | ... |
| 4 | Life | ... |
+------+-------+--------------------------------------+
模块
+------+-------+--------------------------------------+
| mID | mName | content |
+------+-------+--------------------------------------+
| 1 | Data | ... |
| 2 | IT | ... |
| 3 | Math | ... |
| | | ... |
+------+-------+--------------------------------------+
注册
+------+-------+--------------------------------------+
| sID | mID | content |
+------+-------+--------------------------------------+
| 1 | 1 | ... |
| 1 | 2 | ... |
| 1 | 3 | ... |
| 3 | 1 | ... |
+------+-------+--------------------------------------+
由学生分组您的数据,并仅采用具有相同模块计数的人,因为总共有模块
select s.sname
from students s
join Registration r on s.sid = r.sid
group by s.sname
having count(r.mid) = (select count(mid) from modules)
您可以尝试此查询,按SID和名称进行分组:
CREATE TABLE STUD (SID INT, SNAME VARCHAR(20));
CREATE TABLE MODU (MID INT, MNAME VARCHAR(20));
CREATE TABLE REGI (SID INT, MID INT);
INSERT INTO STUD VALUES (1,'Tim');
INSERT INTO STUD VALUES (2,'Boyo');
INSERT INTO STUD VALUES (3,'Karl');
INSERT INTO STUD VALUES (4,'Life');
INSERT INTO STUD VALUES (5,'Tim');
INSERT INTO MODU VALUES (1,'Data');
INSERT INTO MODU VALUES (2,'IT');
INSERT INTO MODU VALUES (3,'Math');
INSERT INTO REGI VALUES (1,1);
INSERT INTO REGI VALUES (1,2);
INSERT INTO REGI VALUES (1,3);
INSERT INTO REGI VALUES (3,1);
INSERT INTO REGI VALUES (5,1);
INSERT INTO REGI VALUES (5,2);
INSERT INTO REGI VALUES (5,3);
SELECT A.SID, A.SNAME
FROM STUD A
INNER JOIN REGI B ON A.SID = B.SID
GROUP BY A.SID, A.SNAME
HAVING COUNT(*) = (SELECT COUNT(*) AS RC_MODULES FROM MODU)
;
输出:
SID SNAME
1 1 Tim
2 5 Tim