成绩分析会用到的一些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:=@rowNum+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:=@rowNum+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:=@rowNum+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:=@rowNum+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 黄正文

（一）说明 1、记录自己面试测试工程师时遇到的一些编程题（python）。 2、回顾下面试的过程，做个总结。 （二）题目 1、输入：JSON {"a":"aa","b":"bb","c":{"d":"dd","e":"ee"}} 输出：字典......

free赖权华
2018/08/03
0
0

05/23
0
0
testing - 测试基本使用接口

testing - 测试基本使用接口 当你写完一个函数，结构体，main之后，你下一步需要的就是测试了。testing包提供了很简单易用的测试包。 写一个基本的测试用例 测试文件的文件名需要以_test.go为...

2018/01/07
0
0
_6_ SQL 用通配符进行过滤

changsanjiang
2018/07/02
0
0

2015/09/21
67
0

19分钟前
2
0

23分钟前
4
0
CPU 读取cache、内存、磁盘性能

google 工程师Jeff Dean 首先在他关于分布式系统的ppt文档列出来的，到处被引用的很多。 1秒=10^3毫秒=10^6微妙=10^9纳秒=10^12皮秒 读 cache ns 级，读内存100 ns，从内存顺序读1MB 0.25ms，...

SibylY
31分钟前
2
0

39分钟前
3
0
《老子》_安身尘世间，做个明白人

1、无为而无不为 出自《道德经·第四十八章》。【译文】处于无为的境界之中，没有什么不能做到的。 2、知者不言，言者不知。 出自《道德经·第五十六章》。【译文】言语无法表达“道”的真意...

43分钟前
2
0 