表 : stu
CREATE TABLE `stu` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`score` int DEFAULT NULL,
`course` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
插入数据:
INSERT INTO `stu` VALUES ('1', '张三', '78', '语文');
INSERT INTO `stu` VALUES ('2', '张三', '82', '数学');
INSERT INTO `stu` VALUES ('3', '李四', '80', '语文');
INSERT INTO `stu` VALUES ('4', '李四', '53', '数学');
INSERT INTO `stu` VALUES ('5', '王五', '81', '语文');
INSERT INTO `stu` VALUES ('6', '王五', '91', '数学');
INSERT INTO `stu` VALUES ('7', '王五', '100', ' 英语');
INSERT INTO `stu` VALUES ('8', '赵六', '100', '语文');
INSERT INTO `stu` VALUES ('9', '赵六', '90', '数学');
分析: 科目分数大于80并且每科都有参与
select name, count(*) from score where score>80 group by name
having count(*) = (select count(DISTINCT course) from score)
结果: