sql解析

 

-- test1
SELECT
s1.class_id as '班级id',
sum(case gender when 'M' then s1.socre else 0 end )/sum(case gender when 'M' then 1 else 0 end ) as '男生平均分',
sum(case gender when 'F' then s1.socre else 0 end )/sum(case gender when 'F' then 1 else 0 end ) as '女生平均分',
avg(s1.socre) as '班级平均分'
FROM
(
select
t1.student_id,
t1.class_id ,
gender,
sum(grade) as socre
from EXAM_T_STUDENT t1 join EXAM_T_GRADE t3 on t1.student_id=t3.student_id join EXAM_T_CLASS t2 on t1.class_id=t2.class_id
where level_val='3'
GROUP BY t1.student_id
)s1
GROUP BY s1.class_id

-- test2
SELECT
level_val as '年级',
sum(case when student_name like'张%' then 1 else 0 end)/count(DISTINCT t1.student_id) as 比例
from EXAM_T_STUDENT t1 join EXAM_T_CLASS t2 on t1.class_id=t2.class_id
GROUP BY level_val

-- test3
SELECT
level_val as '年级',
max(grade) as '语文最高分'
from EXAM_T_STUDENT t1 join EXAM_T_GRADE t3 on t1.student_id=t3.student_id join EXAM_T_CLASS t2 on t1.class_id=t2.class_id
where subject='语文' GROUP BY level_val

-- test4

SELECT
level_val as '年级',
t1.class_id as '班级id'
from EXAM_T_STUDENT t1 join EXAM_T_CLASS t2 on t1.class_id=t2.class_id
GROUP BY level_val
HAVING count(DISTINCT student_id)>3 and sum(case gender when 'F' then 1 else 0 end )>sum(case gender when 'M' then 1 else 0 end )

-- test5

SELECT
t1.student_name as '姓名',
t2.level_val as '年级'
from EXAM_T_STUDENT t1 join EXAM_T_GRADE t3 on t1.student_id=t3.student_id join EXAM_T_CLASS t2 on t1.class_id=t2.class_id where grade>90
GROUP BY t1.student_id
HAVING count(DISTINCT subject)=(SELECT count(DISTINCT subject)from EXAM_T_GRADE GROUP BY t1.student_id )

 

-- test6
select
s1.student_name as 姓名,
s1.score as 总分,
@rank := @rank + 1 as 排名
from (
SELECT
t1.student_id,t1.student_name,sum(grade) as score
from EXAM_T_STUDENT t1 join EXAM_T_GRADE t3 on t1.student_id=t3.student_id join EXAM_T_CLASS t2 on t1.class_id=t2.class_id
where level_val='1'
GROUP BY t1.student_id )s1,(SELECT @rank := 0) s2
ORDER BY s1.score desc
limit 10

 

-- test 7
select
s1.student_name,
s1.class_id,
grade,
@rank := @rank + 1 as 排名
from
(SELECT
t1.student_name,
t2.class_id,
grade
from EXAM_T_STUDENT t1 join EXAM_T_GRADE t3 on t1.student_id=t3.student_id join EXAM_T_CLASS t2 on t1.class_id=t2.class_id
where level_val='1' and subject='数学'
ORDER BY grade DESC)s1 ,(SELECT @rank := 0) s2

 

 

ps：test7没有达到题目要求，需资深人士指导