查询每个学生的各科成绩sql语句
发布网友
发布时间：2022-04-20 02:57
共6个回答
热心网友
时间：2022-04-10 05:49
1、查询每个学生的各科成绩sql语句：
select a.studentid,a.name,a.sex,v1.score as '语文',v2.score as '数学', v3.score as '英语',v4.score
as ‘哲学’, (v1.score+v2.score+v3.score+v4.score)/4 as ‘平均成绩’ from Stuednt a
left join
(select studentid,score from grade where cid=(select cid from course where cname='语文'))as v1
on a.studentid=v1.studentid
left join
(select studentid,score from grade where cid=(select cid from course where cname='数学'))as v2
on a.studentid=v2.studentid
left join
(select studentid,score from grade where cid=(select cid from course where cname='英语'))as v3
on a.studentid=v3.studentid
left join
(select studentid,score from grade where cid=(select cid from course where cname='哲学'))as v4
on a.studentid=v4.studentid
order by a.studentid
2、sql数据库介绍：
（1）SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出"做什么"的命令，"怎么做"是不用使用者考虑的。
（2）SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL。
（3）SQL数据库的数据体系结构基本上是*结构，但使用术语与传统关系模型术语不同。
（4）在SQL中，关系模式(模式)称为"基本表"(base table);存储模式(内模式)称为"存储文件"(stored file);子模式(外模式)称为"视图"(view);元组称为"行"(row);属性称为"列"(column)。
热心网友
时间：2022-04-10 07:07
mysql> select * from student;
+-----------+------+------+
| studentId | name | sex |
+-----------+------+------+
| 1 | 王五 | 男 |
| 2 | 李明 | 女 |
+-----------+------+------+
2 rows in set (0.00 sec)
mysql> select * from course;
+-----+-------+
| cid | cname |
+-----+-------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 哲学 |
+-----+-------+
4 rows in set (0.00 sec)
mysql> select * from grade;
+-----+-----------+------+-------+
| gid | studentId | cid | score |
+-----+-----------+------+-------+
| 1 | 1 | 1 | 80 |
| 2 | 1 | 2 | 70 |
| 3 | 1 | 3 | 80 |
| 4 | 1 | 4 | 90 |
| 5 | 2 | 1 | 90 |
| 6 | 2 | 2 | 70 |
| 7 | 2 | 3 | 70 |
| 8 | 2 | 4 | 80 |
+-----+-----------+------+-------+
8 rows in set (0.00 sec)
mysql> select
-> grade.studentId as '序号',
-> name as '姓名',
-> sex as '性别',
-> sum(if(cname = '语文', score, 0)) as '语文',
-> sum(if(cname = '数学', score, 0)) as '数学',
-> sum(if(cname = '英语', score, 0)) as '英语',
-> sum(if(cname = '哲学', score, 0)) as '哲学',
-> avg(score) as '平均成绩'
-> from
-> grade
-> left join
-> student
-> on grade.studentId = student.studentId
-> left join
-> course
-> on grade.cid = course.cid
-> group by grade.studentId;
+------+------+------+------+------+------+------+----------+
| 序号 | 姓名 | 性别 | 语文 | 数学 | 英语 | 哲学 | 平均成绩 |
+------+------+------+------+------+------+------+----------+
| 1 | 王五 | 男 | 80 | 70 | 80 | 90 | 80.0000 |
| 2 | 李明 | 女 | 90 | 70 | 70 | 80 | 77.5000 |
+------+------+------+------+------+------+------+----------+
2 rows in set (0.00 sec)
mysql 的这样试试，其它数据库就不会了。
追答:D
热心网友
时间：2022-04-10 08:41
select
row_number() over(order by name) as 序号
t.name as 姓名,
t.sex as 性别,
max(case when t.cname='语文' then score end) as 语文,
max(case when t.cname='数学' then score end) as 数学,
max(case when t.cname='英语' then score end) as 英语,
max(case when t.cname='哲学' then score end) as 哲学
from
(select a.name,a.sex,b.cname,c.score
from student a,course b,grade c
where a.studentid=c.studentid
and b.cid=c.cid) as t
group by t.name,t.sex
热心网友
时间：2022-04-10 10:33
select a.studentId,a.name,a.sex,c.cid,b.cname,c.score
into TableA
from Student a, Course b, Grade c
where a.studentId=c.studentId and c.cid=b.cid
select a.studentId,a.name,a.sex,
sum(case cname when "语文" then score else 0 end) as 语文,
sum(case cname when "数学" then score else 0 end) as 数学,
sum(case cname when "英语" then score else 0 end) as 英语,
sum(case cname when "哲学" then score else 0 end) as 哲学,
sum(score)*1.0/4 as "平均成绩"
from TableA
group by name追问这个语句你执行了没，怎么执行不对？
热心网友
时间：2022-04-10 12:41
select a.studentid,a.name,a.sex,v1.score as '语文',v2.score as '数学', v3.score as '英语',v4.score
as ‘哲学’, (v1.score+v2.score+v3.score+v4.score)/4 as ‘平均成绩’ from Stuednt a
left join
(select studentid,score from grade where cid=(select cid from course where cname='语文'))as v1
on a.studentid=v1.studentid
left join
(select studentid,score from grade where cid=(select cid from course where cname='数学'))as v2
on a.studentid=v2.studentid
left join
(select studentid,score from grade where cid=(select cid from course where cname='英语'))as v3
on a.studentid=v3.studentid
left join
(select studentid,score from grade where cid=(select cid from course where cname='哲学'))as v4
on a.studentid=v4.studentid
order by a.studentid追问这个确实能执行，执行的结果与样式的一模一样，不过sql语句太复杂了，能简单些吗？
追答需要简单的结果的过程有时候是很复杂的，其实这个语句还算简单的，只不过每个科目的成绩都是个视图，然后连接起来。
有些查询嵌套多层的子查询，那才复杂呢。
热心网友
时间：2022-04-10 15:05
SELECT 姓名,性别,课程名,成绩 INTO #tab
FROM Student s LEFT JOIN Grade g ON s.studentId=g.studentId LEFT JOIN Course c ON g.cid=c.id
DECLARE @sql VARCHAR(1000)
SET @sql='select 姓名,性别 '
SELECT @sql=@sql+' , max(case #tab.课程名 when '''+a.课程名 +''' then tb.result else 0 end )as ['+ a.课程名+ ']'
FROM (SELECT DISTINCT #tab.课程名 FROM #tab ) AS a
SET @sql=@sql+ ',avg(成绩) from #tab group by #tab.name '
EXEC ( @sql)
追问你的这个SQL语句执行了没？怎么看起来不对
追答CREATE TABLE Student
(
studentId VARCHAR(30),
[name] VARCHAR(30),
sex VARCHAR(20)
)
CREATE TABLE Course
(
cid VARCHAR(20),
cname VARCHAR(30)
)
CREATE TABLE Grade
(
gid VARCHAR(30),
studentId VARCHAR(30),
cid VARCHAR(20),
score INT
)
INSERT INTO Student
SELECT '1','王五' , '男' UNION ALL
SELECT '2','李明' , '女'
go
INSERT INTO Course
SELECT '1' , '语文' UNION ALL
SELECT '2' , '数学' UNION ALL
SELECT '3' , '英语' UNION ALL
SELECT '4' , '哲学'
go
Insert INTO Grade
SELECT '1' ,'1', '1','80' UNION ALL
SELECT '2' ,'1', '2','70' UNION ALL
SELECT '3' ,'1', '3','80' UNION ALL
SELECT '4' ,'1', '4','90' UNION ALL
SELECT '5' ,'2', '1','90' UNION ALL
SELECT '6' ,'2', '2','70' UNION ALL
SELECT '7' ,'2', '3','70' UNION ALL
SELECT '8' ,'2', '4','80'
SELECT [name],sex,cname,score INTO #tab
FROM Student s LEFT JOIN Grade g ON s.studentId=g.studentId LEFT JOIN Course c ON g.cid=c.cid
