MySQL实战—学生信息的基本操作

MySQL实战—学生信息的基本操作

首先，我们有如下四张表：

表1：学生 t_student

student_id	class_id	student_name
1	1	刘艺
2	1	陈耳
3	1	张山
4	1	李思
5	2	王武
6	2	赵柳

表2：考核 t_assess_rule

rule_id	class_id	assess_type	scale	descs
1	1	test_score	70	成绩（固定）
2	1	check_in	10	考勤
3	1	task_scale	10	作业完成率
4	1	task_score	10	成绩 成绩
5	2	test_score	70	成绩（固定）
6	2	check_in	15	考勤
7	2	task_scale	15	作业完成率

表3 科目 t_subject

subject_id	subject_name
1	语文
2	数学

表4 成绩 t_performance

performance_id	student_id	subject_id	test_score	check_in	task_scale	task_score
1	1	1	90	100	100	90
2	1	2	67	100	100	78
3	2	1	71	100	100	80
4	2	2	100	100	100	95
5	3	1	85	100	100	90
6	3	2	88	100	100	90
7	4	1	81	100	100	90
8	4	2	78	100	100	88
9	5	1	63	95	100	66
10	5	2	87	95	100	90
11	6	1	84	91	100	82
12	6	2	72	91	100	70

要求：1、算出一班（class_id=1)每个学生各科的成绩
2、一班（class_id=1)每个学生各科成绩的总分排序
3、三、算出一班（class_id=1)总分第一名的学生
4、算出一、二班总分第一的学生

-- 创建表 学生t_student
CREATE TABLE t_student(
  student_id INT(11) PRIMARY KEY,
  class_id INT(11),
  student_name VARCHAR(20)
);

 -- 插入数据
INSERT into t_student  VALUES(1,1,'刘艺);
INSERT into t_student  VALUES(2,1,'陈耳);
INSERT into t_student  VALUES(3,1,'张山);
INSERT into t_student  VALUES(4,1,‘李思');
INSERT into t_student  VALUES(5,2,'王武);
INSERT into t_student  VALUES(6,2,'赵柳);

-- 创建表 考核 t_assess_rule
CREATE TABLE t_assess_rule(
  rule_id INT(11) PRIMARY KEY,
  class_id INT(11),
  assess_type VARCHAR(20),
  scale INT(11),
  descs VARCHAR(20)
);

-- 插入数据
INSERT INTO t_assess_rule VALUES (1,1,'test_score',70,'成绩（固定）');
INSERT INTO t_assess_rule VALUES (2,1,'check_in',10,'考勤');
INSERT INTO t_assess_rule VALUES (3,1,'task_scale',10,'作业完成率');
INSERT INTO t_assess_rule VALUES (4,1,'task_score',10,'成绩 成绩');
INSERT INTO t_assess_rule VALUES (5,2,'test_score',70,'成绩（固定）');
INSERT INTO t_assess_rule VALUES (6,2,'check_in',15,'考勤');
INSERT INTO t_assess_rule VALUES (7,2,'task_scale',15,'作业完成率');

-- 创建表 科目 t_subject
CREATE TABLE t_subject(
   subject_id INT(11) PRIMARY KEY,
   subject_name VARCHAR(20)
);

-- 插入数据
INSERT INTO t_subject VALUES(1,'语文');
INSERT INTO t_subject VALUES(2,'数学');

-- 创建表 成绩 t_performance
CREATE TABLE t_performance(
   performance_id INT(11) PRIMARY KEY,
   student_id INT(11),
   subject_id INT(11),
   test_score INT(11),
   check_in   INT(11),
   task_scale INT(11),
   task_score INT(11)
);

-- 插入数据
INSERT INTO t_performance VALUES(1,1,1,90,100,100,90);
INSERT INTO t_performance VALUES(2,1,2,67,100,100,78);
INSERT INTO t_performance VALUES(3,2,1,71,100,100,80);
INSERT INTO t_performance VALUES(4,2,2,100,100,100,95);
INSERT INTO t_performance VALUES(5,3,1,85,100,100,90);
INSERT INTO t_performance VALUES(6,3,2,88,100,100,90);
INSERT INTO t_performance VALUES(7,4,1,81,100,100,90);
INSERT INTO t_performance VALUES(8,4,2,78,100,100,88);
INSERT INTO t_performance VALUES(9,5,1,63,95,100,66);
INSERT INTO t_performance VALUES(10,5,2,87,95,100,90);
INSERT INTO t_performance VALUES(11,6,1,84,91,100,82);
INSERT INTO t_performance VALUES(12,6,2,72,91,100,70);

个人觉得成绩应该就是考试成绩。不加平时的考勤作业的成绩。

-- 一、算出一班每个学生各科的成绩
SELECT a.student_id,a.class_id,a.student_name,b.subject_name ,c.test_score 
FROM t_student a,t_subject b,t_performance c WHERE a.class_id = 1
AND a.student_id = c.student_id AND b.subject_id = c.subject_id;

-- 二、一班每个学生各科成绩的总分排序
SELECT a.student_id,a.class_id,a.student_name,b.subject_name ,b.subject_id,
SUM(c.test_score* (SELECT d.scale/100 FROM t_assess_rule d WHERE d.class_id = 1 AND d.assess_type = 'test_score')
+c.check_in* (SELECT d.scale/100 FROM t_assess_rule d WHERE d.class_id = 1 AND d.assess_type = 'check_in')
+c.task_scale* (SELECT d.scale/100 FROM t_assess_rule d WHERE d.class_id = 1 AND d.assess_type = 'task_scale')
+c.task_score* (SELECT d.scale/100 FROM t_assess_rule d WHERE d.class_id = 1 AND d.assess_type = 'task_score'))
FROM t_student a,t_subject b,t_performance c WHERE a.class_id = 1
AND a.student_id = c.student_id AND b.subject_id = c.subject_id 
GROUP BY  a.student_id,b.subject_name ORDER BY SUM(c.test_score+c.check_in+c.task_scale+c.task_score) DESC;

-- 三、算出一班总分第一名的学生
SELECT x.student_id,x.class_id,x.student_name,MAX(x.score) AS score FROM
(SELECT a.student_id,a.class_id,a.student_name,b.subject_name ,b.subject_id,
SUM(c.test_score* (SELECT d.scale/100 FROM t_assess_rule d WHERE d.class_id = 1 AND d.assess_type = 'test_score')
+c.check_in* (SELECT d.scale/100 FROM t_assess_rule d WHERE d.class_id = 1 AND d.assess_type = 'check_in')
+c.task_scale* (SELECT d.scale/100 FROM t_assess_rule d WHERE d.class_id = 1 AND d.assess_type = 'task_scale')
+c.task_score* (SELECT d.scale/100 FROM t_assess_rule d WHERE d.class_id = 1 AND d.assess_type = 'task_score')) AS score
FROM t_student a,t_subject b,t_performance c WHERE a.class_id = 1
AND a.student_id = c.student_id AND b.subject_id = c.subject_id 
GROUP BY  a.student_id,b.subject_name ORDER BY score DESC) x;

-- 四、算出一、二班总分第一的学生
SELECT x.student_id,x.class_id,x.student_name,MAX(x.score) AS score  
FROM(
(SELECT a.student_id,a.class_id,a.student_name,SUM(c.test_score+c.check_in+c.task_scale+c.task_score) AS score
 FROM t_student a,t_subject b,t_performance c WHERE a.student_id = c.student_id AND b.subject_id = c.subject_id GROUP BY  a.student_id) x) GROUP BY x.class_id;

第四题没有写准确，暂时没有做出来。