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

怼烎@ 2022-05-21 11:54 260阅读 0赞

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

![70][]

这个就过滤出了，存在student记录数大于1的老师名与记录条数

SELECT \`name\`,COUNT(student) FROM teacher   GROUP BY \`name\` HAVING COUNT(student)>1

结果

![70 1][]

![70 2][]

修改成2

SELECT \`name\`,COUNT(student) FROM teacher   GROUP BY \`name\` HAVING COUNT(student)>2

结果

![70 3][]

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;

结果

![70 4][]![70 5][]

3.这种是，查询出每个老师存在几条记录，最后结果有几条记录，相当于把学生记录数大于2的老师总数查询出来了。

select count(1) from (select count(\`name\`) from teacher group by \`name\` having count(\*)>=2) as t ;

![70 6][]

![70 7][]

4.查询出每个老师对应的，所有学生总和

SELECT \`name\` ,sum(student) FROM teacher GROUP BY \`name\`

![70 8][]

![70 9][]

表结构

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');

[70]: /images/20220521/d25dea46d2194fa8b3b56a0da5181707.png
[70 1]: /images/20220521/88c03904a5a94df1a80eee0ae4b57dac.png
[70 2]: /images/20220521/d19b80c9cc9347fba6d418551e3692eb.png
[70 3]: /images/20220521/dd11e5f515774fb6a95a4017cb8eb878.png
[70 4]: https://img-blog.csdn.net/20180706174714234?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pbnQ2/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 5]: /images/20220521/dda2ffd2e11f4d1a832c6385fc8caf0c.png
[70 6]: /images/20220521/f020f837c8b649389aba9ebabed6165d.png
[70 7]: /images/20220521/0f2de1c9b26947379058826fb2bf22df.png
[70 8]: /images/20220521/67ce79ffabb940879e01349a0e473fe1.png
[70 9]: /images/20220521/1ae0ba8d413f45469dc388ac77329957.png