sql语句查询老师学生记录大于1的条数

最新推荐文章于 2024-07-28 03:32:09 发布

Mint6

最新推荐文章于 2024-07-28 03:32:09 发布

阅读量1.3w

点赞数 2

分类专栏： mysql 文章标签： sql count sum

本文链接：https://blog.csdn.net/mint6/article/details/80944381

版权

mysql 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

这是teacher表结构，查询每个老师student这个字段存在数据库的记录数，过滤出大于2的记录

这个就过滤出了，存在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

结果

select temp.nm,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');
INSERT INTO `teacher` VALUES ('zhangsab', '5', '1');
INSERT INTO `teacher` VALUES ('lisi', '6', '1');