统计每个学生的考试总分按高低分数排序,有重复则按照考试时间先后顺序排序
前言
SQL测试
①学生表A,字段包括:学生编码,学生姓名
②考试表B,字段包括:学生编码,考试科目,考试成绩,考试时间
(2)给考试表A,B表设计最优的索引,请手写出来。
(3)统计每科目最高分的同学姓名及分数,输出结果:科目、最高分、姓名
(4)统计每个学生的考试总分按高低分数排序,有重复则按照考试时间先后顺序排序,输出结果:学生1,总分第一;学生2,总分第二;
一、建表
①学生表A,字段包括:学生编码,学生姓名
②考试表B,字段包括:学生编码,考试科目,考试成绩,考试时间。
给考试表A,B表设计最优的索引,请手写出来。
student_A 学生表A
id
stu_code 学生编码
stu_name 学生姓名 (binary INDEX)
test 测试表
id
name
student_test_B 考试表B
id
test_score 考试成绩
test_date 考试时间
test_id test_foreign_key
stu_id sutdent_A_foreign_key
二、使用步骤
1.引入库
代码如下:
SELECT g.name as 学生,
g.test_sum_score as 总分
from (
SELECT (CASE WHEN
d.test_sum_score=e.test_sum_score
and d.id = e._id
THEN d.test_score
ELSE END as order_by_score),
(CASE WHEN
d.test_sum_socre=e.test_sum_score
and d.id <> e._id
THEN d.test_date
ELSE END as order_by_date),
f.name,
f.test_sum_socre
FROM (
SELECT *
FROM(
SELECT a.id as id,c.name,sum(b.test_score) as test_sum_socre,a.stu_name
FROM student_A a
LEFT JOIN student_test_B b
ON a.id=b.stu_id
right JOIN test c
ON c.id=b.test_id
group by b.stu_id, b.test_id
) d
WHERE EXISTS(
SELECT *
FROM (
SELECT a.id as _id,c.name,sum(b.test_score) as test_sum_socre,a.stu_name
FROM student_A a
LEFT JOIN student_test_B b
ON a.id=b.stu_id
right JOIN test c
ON c.id=b.test_id
GROUP BY b.stu_id, b.test_id
) e
where d.id=e._id
)
) f
ORDER BY f.order_by_score DESC
ORDER BY f.order_by_date ASC
) g;
总结
完成项目的需求,首先需要写sql脚本组装字段数据。前端需要什么字段的数据,后台的数据库里就提供什么数据。
整体思路:
1. 建表。
2. 明白表与表之间的关联关系。
3. 主从表(父子表),左右表。
4. 写sql脚本。
5. 适当使用索引。