mysql统计-关于学生成绩
转载地址http://blog.csdn.net/suntanyong88/article/details/7683089问题:1. 计算每个人的总成绩并排名(要求显示字段:姓名,总成绩)2. 计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)3. 计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)4. 计算每
·
<span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">注意前5个问题使用张三和李四两个字段</span>
1.计算每个人的总成绩并排名(要求显示字段:姓名,总成绩)
SELECT name,SUM(score)
from stuscore
GROUP BY `name`
ORDER BY SUM(score) ASC
2.
SELECT stuid,`name`,SUM(score)
FROM stuscore
GROUP BY `name`
ORDER BY SUM(score)
|
|
|
1 | 张三 | 239 |
2 | 李四 | 240 |
3.
写法1
SELECT stuid,`name`,`subject`,max(score)
FROM stuscore
group by stuid
写法2:
SELECT t1.stuid,t1.`name`,t1.`subject`,t1.score
FROM stuscore t1,
(SELECT `stuid`,MAX(score) AS maxscore FROM stuscore GROUP BY `stuid`) t2
where t1.stuid=t2.stuid and t1.score=t2.maxscore
stuid | name | subject | score |
1 | 张三 | 数学 | 89 |
2 | 李四 | 数学 | 90 |
4.
SELECT stuid,`name`,AVG(score)
FROM stuscore
group by stuid
stuid | name |
|
1 | 张三 | 79.6667 |
2 | 李四 | 80.0000 |
5.
方法一:
SELECT stuid,`name`,`subject`,max(score)
FROM stuscore
GROUP BY `subject`
方法二:
SELECT t1.stuid,t1.`name`,t1.`subject`,t2.maxscore
FROM stuscore t1,(SELECT `subject`,MAX(score)as maxscore FROM stuscore GROUP BY `subject`)t2
WHERE t1.`subject`=t2.`subject` and t1.score=t2.maxscore
stuid | name | subject | maxscore |
1 | 张三 | 语文 | 80 |
2 | 李四 | 数学 | 90 |
2 | 李四 | 英语 | 80 |
从问题6开始增加王五字段
6.
select stuid,`name`,SUM(score)as sumscore
from stuscore
GROUP BY `name`
order by sumscore DESC
LIMIT 2
stuid | name | sumscore |
3 | 王五 | 268 |
2 | 李四 | 240 |
7.列出数学成绩的排名
select `name`,`subject`,score
from stuscore
where `subject`="数学"
ORDER BY score DESC
8.
求出李四的数学成绩的排名
9.统计学科成绩及格,良,优的个数
select subject,
(select count(*) from stuscore where score<60 and subject=t1.subject) as 不及格,
(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良,
(select count(*) from stuscore where score >80 and subject=t1.subject) as 优
from stuscore t1 <span style="font-family: 宋体;">group by subject</span>
subject | 及格 | 良 | 优 |
数学 | 0 | 0 | 3 |
英语 | 0 | 2 | 1 |
语文 | 0 | 2 | 1 |
</pre><pre name="code" class="sql">select stuid as 学号,name as 姓名,
sum(case when subject='语文' then score else 0 end) as 语文,
sum(case when subject='数学' then score else 0 end) as 数学,
sum(case when subject='英语' then score else 0 end) as 英语,
sum(score) as 总分,(sum(score)/count(*)) as 平均分
from stuscore
group by stuid,name
更多推荐
已为社区贡献1条内容
所有评论(0)