drop table if exists STU_SCORE;
create table STU_SCORE
(
name varchar(50) comment '姓名',
subject varchar(50) comment '课程',
score numeric(11,2) comment '分数',
stuid int comment '学号'
)ENGINE = MYISAM DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
alter table STU_SCORE comment '学生成绩表';
INSERT into STU_SCORE VALUES('张三','数学',89,1);
INSERT into STU_SCORE VALUES('张三','语文',90,1);
INSERT into STU_SCORE VALUES('张三','英语',50,1);
INSERT into STU_SCORE VALUES('李四','数学',90,2);
INSERT into STU_SCORE VALUES('李四','语文',93,2);
INSERT into STU_SCORE VALUES('李四','英语',80,2);
INSERT into STU_SCORE VALUES('王五','数学',95,3);
INSERT into STU_SCORE VALUES('王五','语文',59,3);
INSERT into STU_SCORE VALUES('王五','英语',88,3);
INSERT into STU_SCORE VALUES('赵六','数学',98,4);
INSERT into STU_SCORE VALUES('赵六','语文',69,4);
INSERT into STU_SCORE VALUES('赵六','英语',78,4);
1.1. 计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)
#1.1.计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)
SELECT stuid AS '学号', name AS '姓名',SUM(score) AS '总成绩'
FROM STU_SCORE GROUP BY stuid ORDER BY '总成绩' DESC
1.2. 列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,最高分
#1.2.列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,最高分
SELECT stuid AS '学号', name AS '姓名' ,`subject` AS '科目',MAX(score) AS '最高分'
FROM STU_SCORE GROUP BY SUBJECT ORDER BY '最高分' DESC
1.3. 统计如下:学号 姓名 语文 数学 英语 总分 平均分
#1.3.统计:学号 姓名 语文 数学 英语 总分 平均分
SELECT stuid as '学号',name as '姓名',
sum(case when subject='语文' then score else 0 end) as '语文',
sum(case when subject='数学' then score else 0 end) as '数学',
sum(case when subject='英语' then score else 0 end) as '英语',
sum(score) as '总分',
(sum(score)/count(1)) as '平均分'
FROM STU_SCORE GROUP BY stuid ORDER BY 总分 DESC
1.4. 统计如下:课程 不及格(0-59) 良(60-80) 优(81-100)
#1.4.统计如下:课程 不及格(0-59) 良(60-80) 优(81-100)
SELECT subject AS '课程',
(SELECT count(1) FROM STU_SCORE WHERE score<60 AND subject=t1.subject) AS '不及格',
(SELECT count(1) FROM STU_SCORE WHERE score BETWEEN 60 AND 80 AND subject = t1.subject) AS '良',
(SELECT count(1) FROM STU_SCORE WHERE score >80 AND subject=t1.subject) AS '优'
FROM STU_SCORE t1 GROUP BY subject
1.5. 统计如下
本帖转载于:http://www.360doc.com/content/14/0513/15/16319058_377247264.shtml