成绩分析会用到的一些SQL

学生、考试、科目、课程

数据据结构如图：

-- 查询某次考试的单科成线
select * from score where student = '1' and exam ='半期' and course = '语文'

-- 成绩分级
select student ,if(score<90,'不及格',if(score>=90 and score <= 110, '良好', '优秀')) as '等级' from score

-- 查询总成绩
select student,exam,SUM(score) as '总成绩' from score  where student = '1' GROUP BY student,exam

-- 时间段内的考试的单科成绩
-- select student,score from score where student='1' and course='语文' and time <'2013-01-01' and time>'2012-03-03' ORDER BY time asc

--考试成绩单
-- SELECT  student ,max(case course when '语文' then score else 0 end) as '语文',max(case course when '数学' then score else 0 end) as  '数学' FROM score WHERE student='1' and exam='半期'

--考试成绩单（取历次考试的最优成绩)
 -- SELECT  student ,max(case course when '语文' then score else 0 end) as '语文',max(case course when '数学' then score else 0 end) as  '数学' FROM score WHERE student='1'

-- 查询单科整体成绩排名
select student,exam,course,score,(@rowNum:[email protected]+1) as '排名' from score,(Select (@rowNum :=0)) b where exam ='半期' and course='语文' ORDER BY score desc

-- 查询单科成绩个体名次select student,exam,course,排名 from(select student,exam,course,score,(@rowNum:[email protected]+1) as '排名' from score,(Select (@rowNum :=0)) b where exam ='半期' and course='语文' ORDER BY score desc) as ss where student = '2'

-- 给成绩单加上总成绩
-- select *,语文+数学 as '总成绩' from(SELECT  exam,student , max(case course when '语文' then score else 0 end) as '语文',max(case course when '数学' then score else 0 end) as  '数学' FROM score GROUP BY student ,exam order by exam,student) as temp where exam ='半期'

-- 给成绩单加上平均成绩
-- select *,语文+数学 as '总成绩' ,(语文+数学)/2 as '平均成绩' from(SELECT  exam,student , max(case course when '语文' then score else 0 end) as '语文',max(case course when '数学' then score else 0 end) as  '数学' FROM score GROUP BY student ,exam order by exam,student) as temp where exam ='半期'

-- 给成绩单加上总成绩排名
-- select student,语文,数学,总成绩,(@rowNum:[email protected]+1) as '排名' from(
-- select *,语文+数学 as '总成绩' from(
-- SELECT  exam,student , max(case course when '语文' then score else 0 end) as '语文',max(case course when '数学' then score else 0 end) as  '数学' FROM score GROUP BY student ,exam order by exam,student
-- ) as temp where exam='半期'
-- ) as tempp ,(Select (@rowNum :=0)) b
--
-- 查询个体总成绩排名
-- select * from
-- (
--  select student,语文,数学,总成绩,(@rowNum:[email protected]+1) as '排名'
--  from
--  (
--   select *,语文+数学 as '总成绩'
--   from
--   (
--    SELECT  exam,student , max(case course when '语文' then score else 0 end) as '语文',max(case course when '数学' then score else 0 end) as  '数学'
--    FROM score
--    GROUP BY student ,exam
--    order by exam,student
--   ) as temp where exam='半期' ORDER BY '总成绩'
--  ) as tempp ,(Select (@rowNum :=0)) b
-- ) as temppp
-- where student='2'

-- 及格率

select count(id) as '及格人数',totalnum as '总人数',count(id)/totalnum as '及格率'  from (
select count(id) as 'totalnum' ,id,score from score where exam = '半期' and course = '语文'
) as total where score >125

-- 及格率(上面两个有错)

select count(id) as '及格人数',totalnum as '总人数',CONCAT(conv(FORMAT(count(id)/totalnum,4)*100,10,10),'%') as '及格率'  from (
select count(id) as 'totalnum' ,id,score from score where exam = '半期' and course = '语文'
) as total where score >125

select CONCAT(cast(sum(jige)/count(*)*100 as decimal(4,2)),'%') as '及格率'
from (
select id,if(score>= 130,1,0) as jige from score where exam = '半期' and course = '语文'
) as total

-- 及格率

select count(*) as '总人数',sum(jige) as '及格人数',CONCAT(cast(sum(jige)/count(*)*100 as decimal(4,2)),'%') as '及格率',exam as '考试',course as '课程'
from (
 select id,if(score>= 100,1,0) as jige,exam,course from score
 ) as total group by exam,course