用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插入如下数据
来源:学生作业帮助网 编辑:作业帮 时间:2024/05/11 19:11:02
用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插入如下数据
用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
下面的不对啊
用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插入如下数据
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
即可