问题如下:
有三张表:class、student、score班级表 class: +-------+---------+---------+
| classID | className |
+---------+-----------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
+---------+-----------+学生表 student:
+-------+---------+---------+
| stuID | classID | stuName |
+-------+---------+---------+
| 1001 | 1 | 张三 |
| 1002 | 1 | 李丽 |
| 1003 | 1 | 钱封 |
| 1004 | 2 | 杨国 |
| 1005 | 2 | 小样 |
| 1006 | 2 | 区天 |
| 1007 | 3 | 李三宅 |
| 1008 | 3 | 黄武 |
| 1009 | 3 | 赵六 |
+-------+---------+---------+分数表 score:
+----------+-------+--------+-------+
| courseID | stuID | course | score |
+----------+-------+--------+-------+
| 2 | 1001 | 数学 | 73 |
| 3 | 1001 | 英语 | 79 |
| 1 | 1001 | 语文 | 81 |
| 3 | 1002 | 英语 | 87 |
| 2 | 1002 | 数学 | 83 |
| 1 | 1002 | 语文 | 79 |
| 1 | 1003 | 语文 | 65 |
| 3 | 1003 | 英语 | 65 |
| 2 | 1003 | 数学 | 97 |
| 1 | 1004 | 语文 | 78 |
| 3 | 1004 | 英语 | 78 |
| 2 | 1004 | 数学 | 86 |
| 1 | 1005 | 语文 | 67 |
| 3 | 1005 | 英语 | 88 |
| 2 | 1005 | 数学 | 89 |
| 2 | 1006 | 数学 | 90 |
| 3 | 1006 | 英语 | 92 |
| 1 | 1006 | 语文 | 98 |
| 1 | 1007 | 语文 | 85 |
| 2 | 1007 | 数学 | 78 |
| 3 | 1007 | 英语 | 72 |
| 1 | 1008 | 语文 | 78 |
| 3 | 1008 | 英语 | 77 |
| 2 | 1008 | 数学 | 85 |
| 3 | 1009 | 英语 | 94 |
| 2 | 1009 | 数学 | 91 |
| 1 | 1009 | 语文 | 68 |
+----------+-------+--------+-------+
1、查询各班各科分数最高的学生学号,姓名,班级名称,科目名称,分数?
有三张表:class、student、score班级表 class: +-------+---------+---------+
| classID | className |
+---------+-----------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
+---------+-----------+学生表 student:
+-------+---------+---------+
| stuID | classID | stuName |
+-------+---------+---------+
| 1001 | 1 | 张三 |
| 1002 | 1 | 李丽 |
| 1003 | 1 | 钱封 |
| 1004 | 2 | 杨国 |
| 1005 | 2 | 小样 |
| 1006 | 2 | 区天 |
| 1007 | 3 | 李三宅 |
| 1008 | 3 | 黄武 |
| 1009 | 3 | 赵六 |
+-------+---------+---------+分数表 score:
+----------+-------+--------+-------+
| courseID | stuID | course | score |
+----------+-------+--------+-------+
| 2 | 1001 | 数学 | 73 |
| 3 | 1001 | 英语 | 79 |
| 1 | 1001 | 语文 | 81 |
| 3 | 1002 | 英语 | 87 |
| 2 | 1002 | 数学 | 83 |
| 1 | 1002 | 语文 | 79 |
| 1 | 1003 | 语文 | 65 |
| 3 | 1003 | 英语 | 65 |
| 2 | 1003 | 数学 | 97 |
| 1 | 1004 | 语文 | 78 |
| 3 | 1004 | 英语 | 78 |
| 2 | 1004 | 数学 | 86 |
| 1 | 1005 | 语文 | 67 |
| 3 | 1005 | 英语 | 88 |
| 2 | 1005 | 数学 | 89 |
| 2 | 1006 | 数学 | 90 |
| 3 | 1006 | 英语 | 92 |
| 1 | 1006 | 语文 | 98 |
| 1 | 1007 | 语文 | 85 |
| 2 | 1007 | 数学 | 78 |
| 3 | 1007 | 英语 | 72 |
| 1 | 1008 | 语文 | 78 |
| 3 | 1008 | 英语 | 77 |
| 2 | 1008 | 数学 | 85 |
| 3 | 1009 | 英语 | 94 |
| 2 | 1009 | 数学 | 91 |
| 1 | 1009 | 语文 | 68 |
+----------+-------+--------+-------+
1、查询各班各科分数最高的学生学号,姓名,班级名称,科目名称,分数?
select t2.tuName, t1.className, t3.course, t3.score,
row_number()over(partition by t1.className, t3.course order by t3.score) rn
from class t1,student t2,score t3
where t1.classID=t2.classID and t2.stuID=t3.stuID
and rn=1--上面的sql使用了分析函数row_number(),这个函数返回唯一值,碰到相同分数时,会根据顺序依次递增,如果相同分数希望返回相同顺序时,使用dense_rank()即可
--这个是不用分析函数的,使用group by就可以了,但是如果分数相同的话,只能取其中的一个作为最高分
select t2.tuName, t1.className, t3.course, max(t3.score)
from class t1,student t2,score t3
where t1.classID=t2.classID and t2.stuID=t3.stuID
group by t1.className, t3.course, t2.tuName
--这是一班的各科分数最高的学生学号,姓名,班级名称,科目名称,分数
稍微改一下,union all一下就可以是实现LZ的要求的,不过这算作弊嘛?
select sc.stuid,stu.stuname,'一班' classname,sc.course,sc.score from score sc inner join (select max(score) mscore,course from score where stuid in(1001,1002,1003) group by course) t on t.mscore=sc.score inner join student stu on stu.stuid=sc.stuid
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`classID` int(11) DEFAULT NULL,
`className` varchar(20) DEFAULT NULL
)
INSERT INTO `class` VALUES ('1', '一班');
INSERT INTO `class` VALUES ('2', '二班');
INSERT INTO `class` VALUES ('3', '三班');DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stuID` int(11) DEFAULT NULL,
`classID` int(11) DEFAULT NULL,
`stuName` varchar(20) DEFAULT NULL
)
INSERT INTO `student` VALUES ('1004', '2', '杨国');
INSERT INTO `student` VALUES ('1005', '2', '小样');
INSERT INTO `student` VALUES ('1006', '2', '区天');
INSERT INTO `student` VALUES ('1007', '3', '李三宅');
INSERT INTO `student` VALUES ('1008', '3', '黄武');
INSERT INTO `student` VALUES ('1009', '3', '赵六');
INSERT INTO `student` VALUES ('1001', '1', '张三');
INSERT INTO `student` VALUES ('1002', '1', '李丽');
INSERT INTO `student` VALUES ('1003', '1', '钱封');DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`courseID` int(11) DEFAULT NULL,
`stuID` int(11) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`score` int(11) DEFAULT NULL
)
INSERT INTO `score` VALUES ('1', '1004', '语文', '78');
INSERT INTO `score` VALUES ('1', '1005', '语文', '67');
INSERT INTO `score` VALUES ('1', '1006', '语文', '98');
INSERT INTO `score` VALUES ('2', '1004', '数学', '86');
INSERT INTO `score` VALUES ('2', '1005', '数学', '89');
INSERT INTO `score` VALUES ('2', '1006', '数学', '90');
INSERT INTO `score` VALUES ('3', '1004', '英语', '78');
INSERT INTO `score` VALUES ('3', '1005', '英语', '88');
INSERT INTO `score` VALUES ('3', '1006', '英语', '92');
INSERT INTO `score` VALUES ('1', '1007', '语文', '85');
INSERT INTO `score` VALUES ('1', '1008', '语文', '78');
INSERT INTO `score` VALUES ('1', '1009', '语文', '68');
INSERT INTO `score` VALUES ('2', '1007', '数学', '78');
INSERT INTO `score` VALUES ('2', '1008', '数学', '85');
INSERT INTO `score` VALUES ('2', '1009', '数学', '91');
INSERT INTO `score` VALUES ('3', '1007', '英语', '72');
INSERT INTO `score` VALUES ('3', '1008', '英语', '77');
INSERT INTO `score` VALUES ('3', '1009', '英语', '94');
INSERT INTO `score` VALUES ('1', '1001', '语文', '81');
INSERT INTO `score` VALUES ('1', '1002', '语文', '79');
INSERT INTO `score` VALUES ('1', '1003', '语文', '65');
INSERT INTO `score` VALUES ('2', '1001', '数学', '73');
INSERT INTO `score` VALUES ('2', '1002', '数学', '83');
INSERT INTO `score` VALUES ('2', '1003', '数学', '97');
INSERT INTO `score` VALUES ('3', '1001', '英语', '79');
INSERT INTO `score` VALUES ('3', '1002', '英语', '87');
INSERT INTO `score` VALUES ('3', '1003', '英语', '65');