用T-sql语句写出 查询出平均分大于80分,且至少两门课大于80的学生.表如下:
来源:学生作业帮 编辑:作业帮 分类:综合作业 时间:2024/05/17 23:05:46
用T-sql语句写出 查询出平均分大于80分,且至少两门课大于80的学生.表如下:
create table student
(
id int primary key ,
name varchar(10) not null,
major varchar(10) not null,
score int ,
sex varchar(2) not null
)
go
插入如下数据:
insert into student values(1,'邱君',70,'女')
insert into student values(2,'小狗',76,'男')
insert into student values(3,'混蛋',60,'男')
insert into student values(4,'邱君',81,'女')
insert into student values(5,'混蛋',90,'男')
insert into student values(6,'小狗',77,'男')
insert into student values(7,'邱君','外语',98,'女')
insert into student values(8,'小狗','外语',71,'男')
insert into student values(9,'混蛋','外语',88,'男')
go
输出如下图形:
姓名 外语 平均成绩
邱君 81 98 76 82
下面的不对啊
create table student
(
id int primary key ,
name varchar(10) not null,
major varchar(10) not null,
score int ,
sex varchar(2) not null
)
go
插入如下数据:
insert into student values(1,'邱君',70,'女')
insert into student values(2,'小狗',76,'男')
insert into student values(3,'混蛋',60,'男')
insert into student values(4,'邱君',81,'女')
insert into student values(5,'混蛋',90,'男')
insert into student values(6,'小狗',77,'男')
insert into student values(7,'邱君','外语',98,'女')
insert into student values(8,'小狗','外语',71,'男')
insert into student values(9,'混蛋','外语',88,'男')
go
输出如下图形:
姓名 外语 平均成绩
邱君 81 98 76 82
下面的不对啊
SELECT id,
SUM(CASE WHEN major = '数学' THEN score ELSE 0 END) ,
SUM(CASE WHEN major = '外语' THEN score ELSE 0 END) ,
SUM(CASE WHEN major = '语文' THEN score ELSE 0 END) ,
AVG(score)
FROM student
WHERE student.id IN (SELECT id FROM student GROUP BY id HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2)
GROUP BY id HAVING AVG(score) > 80
即可
再问: 不对啊
再答: 什么问题?
再问: SELECT name, SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 , SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 , SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文 FROM student WHERE name IN (SELECT name FROM student GROUP BY name HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2) GROUP BY name HAVING AVG(score) > 80 我把你的改了一下就好了 谢谢你的回答!
再答: 恩。我也刚发现了。 你的少了一个平均成绩 SELECT name, SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 , SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 , SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文, AVG(score) FROM student WHERE name IN (SELECT name FROM student GROUP BY name HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2) GROUP BY name HAVING AVG(score) > 80 3楼那样要简化点,不过貌似having加多条件过滤后反而慢 SELECT name, SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 , SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 , SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文, AVG(score) FROM student GROUP BY name HAVING AVG(score) > 80 AND SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2
SUM(CASE WHEN major = '数学' THEN score ELSE 0 END) ,
SUM(CASE WHEN major = '外语' THEN score ELSE 0 END) ,
SUM(CASE WHEN major = '语文' THEN score ELSE 0 END) ,
AVG(score)
FROM student
WHERE student.id IN (SELECT id FROM student GROUP BY id HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2)
GROUP BY id HAVING AVG(score) > 80
即可
再问: 不对啊
再答: 什么问题?
再问: SELECT name, SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 , SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 , SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文 FROM student WHERE name IN (SELECT name FROM student GROUP BY name HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2) GROUP BY name HAVING AVG(score) > 80 我把你的改了一下就好了 谢谢你的回答!
再答: 恩。我也刚发现了。 你的少了一个平均成绩 SELECT name, SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 , SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 , SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文, AVG(score) FROM student WHERE name IN (SELECT name FROM student GROUP BY name HAVING SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2) GROUP BY name HAVING AVG(score) > 80 3楼那样要简化点,不过貌似having加多条件过滤后反而慢 SELECT name, SUM(CASE WHEN major = '数学' THEN score ELSE 0 END)数学 , SUM(CASE WHEN major = '外语' THEN score ELSE 0 END)外语 , SUM(CASE WHEN major = '语文' THEN score ELSE 0 END)语文, AVG(score) FROM student GROUP BY name HAVING AVG(score) > 80 AND SUM(CASE WHEN score > 80 THEN 1 ELSE 0 END) >= 2
用T-sql语句写出 查询出平均分大于80分,且至少两门课大于80的学生.表如下:
(1)用SQL语句写出张姓且单名的学生的学号、姓名.(2)用SQL语句统计大于平均分的学生人数.
SQL查询中,要将"学生"表中女生的入学成绩加10分,可使用的语句是
sql 语言查询 查询出有两门不及格的学生信息. 查询各个班级的各门课程的平均分
从学生选课数据库中查询选修“数据库原理”课并且成绩在90分以上的学生名单,请写出SQL语句.
用SQL表达“查询出2008年1到3月份订单总金额大于200000的雇员”表:orders,order_details,
在SQL server中查询每个学生的班级、学号、姓名、平均分,结果按平均分降序排列,平均分相同者按班级排列
SQL查询每门课的平均分
excle用函数求出结论,结论条件为:平均分大于等于90分的为优秀,平均分大于等于80分的
T-SQL查询成绩表的平均分,如果平均分低于60分,为每一位同学加1分,重复该操作,直到平均分为60
SQL查询语句,要得到每个班每门课程的平均分,以及每个班的人数.如图:
怎么用SQL语句查询student表中年龄最小的前三名学生信息?