Question

有10个学生成绩，求平均分,最低分,最高分并排序,要求写四个函数并由主函数调用

用c++

Answer 1

---1. 计算每个人的总成绩并排名（要求显示字段：姓名，总成绩）
select name,sum(cast(score as bigint)) as allscore from stuscore group by name order by allscore desc
---2. 计算每个人的总成绩并排名（要求显示字段: 学号，姓名，总成绩）
select stuid,name,sum(cast(score as bigint)) as allscore from stuscore group by stuid,name order by allscore desc
---3. 计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)
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
---4. 计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）
select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,(select stuid,avg(cast(score as bigint)) as avgscore from stuscore group by stuid) t2 where t1.stuid=t2.stuid
---5. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)
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
---6. 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)
select distinct t1.* from stuscore t1 where t1.stuid in(select top 2 stuscore.stuid from stuscore where subject = t1.subject order by score desc)order by t1.subject
---7. 统计报表(要求显示字段: 学号，姓名，各科成绩，总分，平均成绩)
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(cast(score as bigint)) as 总分,(sum(cast(score as bigint))/count(*)) as 平均分 from stuscore group by stuid,name order by 总分 desc
---8. 列出各门课程的平均成绩（要求显示字段：课程，平均成绩）
select subject,avg(cast(score as bigint)) as avgscore from stuscore group by subject
---9. 列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）
select * from stuscore where subject ='数学' order by score desc
---10. 列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）
select t3.* from(select top 2 t2.* from (select top 3 name,subject,score,stuid from stuscore where subject='数学' order by score desc) t2 order by t2.score) t3 order by t3.score desc

追问

你这个是啥呀

追答

统计信息用到的数据库查询语句