sql - group & having example
程序员文章站
2022-06-05 18:21:30
...
problem:
there is table store course score of students, now write a sql to query student who has at least 2 course failed, means score<60,
sql: (mysql)
# drop table drop table if exists score; # create table create table score( id integer AUTO_INCREMENT primary key, student_num varchar(32) not null, course_name varchar(64), course_score float, unique key(student_num, course_name) ) engine=MYISAM DEFAULT CHARSET=utf8; # clear data truncate score; # prepare data insert into score(student_num, course_name, course_score) values ("001", "English", 100), ("001", "Math", 100), ("002", "English", 90), ("002", "Math", 90), ("003", "English", 20), ("003", "Math", 20), ("003", "Music", 20), ("004", "English", 20), ("004", "Math", 20), ("005", "English", 70), ("005", "Math", 30); # query select student_num, count(course_name) as cc from score where course_score < 60 group by student_num having cc>=2
推荐阅读
-
MySQL5.7+版本,提示mysql1055错误,sql_mode=only_full_group_by问题解决办法
-
MySQL:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY with sql_mode=only_full_group
-
Error 1140: In aggregated query without GROUP BY,sql_mode=only_full_group_by
-
this is incompatible with sql_mode=only_full_group_by
-
mysql执行sql语句提示Expression #1 of ORDER BY clause is not in GROUP BY
-
MySQL 报错:#1 of ORDER BY clause is not in GROUP BY sql_mode=only_full_group_by
-
mysql 报错 this is incompatible with sql_mode=only_full_group_by
-
Mysql数据库报错:select list is not in group by clause and contains .....sql_mode=only_full_group_by问题
-
sql_mode=only_full_group_by问题
-
解决MySQL 5.7.9版本sql_mode=only_full_group_by问题