sql语句查询⽼师学⽣记录⼤于1的条数
这是teacher表结构，查询每个⽼师student这个字段存在数据库的记录数，过滤出⼤于2的记录
1.
这个就过滤出了，存在student记录数⼤于1的⽼师名与记录条数
SELECT `name`,COUNT(student) FROM teacher  GROUP BY `name` HAVING COUNT(student)>1
结果
修改成2
SELECT `name`,COUNT(student) FROM teacher  GROUP BY `name` HAVING COUNT(student)>2
结果
2.
,temp.st FROM (SELECT `name` as nm ,COUNT(`student`) as st FROM teacher GROUP BY `name`) as temp where temp.st>1;
结果
3.这种是，查询出每个⽼师存在⼏条记录，最后结果有⼏条记录，相当于把学⽣记录数⼤于2的⽼师总数查询出来了。
select count(1) from (select count(`name`) from teacher group by `name` having count(*)>=2) as t ;
4.查询出每个⽼师对应的，所有学⽣总和
SELECT `name` ,sum(student) FROM teacher GROUP BY `name`
表结构
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`name` varchar(255) DEFAULT NULL,
`class` int(10) DEFAULT NULL,
`student` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('zhangsan', '1', '2');
INSERT INTO `teacher` VALUES ('lisi', '1', '5');
INSERT INTO `teacher` VALUES ('lisi', '2', '3');
INSERT INTO `teacher` VALUES ('zhangsan', '8', '6');
sql语句查询结果取反
INSERT INTO `teacher` VALUES ('zhangsab', '5', '1');
INSERT INTO `teacher` VALUES ('lisi', '6', '1');

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。