13,100
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `c` (
`cid` int(11) DEFAULT NULL,
`cname` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `c` VALUES (1,'class1'),(2,'class2'),(3,'class3');
CREATE TABLE `s` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(25) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `s` VALUES (1,'stu1'),(2,'stu2'),(3,'stu3');
CREATE TABLE `sc` (
`sid` int(11) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
`cnum` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `sc` VALUES (1,1,11),(1,2,21),(2,1,21),(2,2,21),(2,3,21),(3,2,21);
select sid ,sname from s where sid in ( select mid._sid as msid from (select sid as _sid ,count(cid) as _count from sc group by(sid)) as mid where mid._count=(select count(*) from c ));