社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
简单描述:
成绩表中有学生姓名、学生科目以及学生分数。查询分数最高和分数最低的学生的以及学生的分数。注意的点如下:
1、group by,select 选择的列,必须是group by 中的列,或者是函数形式。
2、利用MAX() 和MIN()sql函数。
3、建立的表结构如下。
-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`stu_name` varchar(255) NOT NULL,
`stu_subject` varchar(255) NOT NULL,
`score` int(255) DEFAULT NULL,
PRIMARY KEY (`stu_subject`,`stu_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('lilei', 'shuxue', '99');
INSERT INTO `grade` VALUES ('xiaoyong', 'shuxue', '66');
INSERT INTO `grade` VALUES ('lilei', 'yingyu', '80');
INSERT INTO `grade` VALUES ('xiaoyong', 'yingyu', '55');
INSERT INTO `grade` VALUES ('lilei', 'yuwen', '78');
INSERT INTO `grade` VALUES ('xiaoyong', 'yuwen', '88');
思路
1)按照科目分组查询成绩最高的人
SELECT grade.stu_subject, MAX(grade.score) as score from grade GROUP BY stu_subject;
2)显示出成绩最高的人的姓名和科目名称
SELECT grade.* from grade,
(SELECT grade.stu_subject, MAX(grade.score) as score from grade GROUP BY stu_subject )b
WHERE grade.score = b.score and grade.stu_subject = b.stu_subject;
3)显示出列标题.
SELECT “成绩最高”,grade.* from grade,
(SELECT grade.stu_subject, MAX(grade.score) as score from grade GROUP BY stu_subject )b
WHERE grade.score = b.score and grade.stu_subject = b.stu_subject;
成绩最低的也是如此就是利用MIN()函数, 如果需要同时查出, 可以用union 如下sql
SELECT "成绩最高",grade.* from grade,
(SELECT grade.stu_subject, MAX(grade.score) as score from grade GROUP BY stu_subject )b
WHERE grade.score = b.score and grade.stu_subject = b.stu_subject
UNION
SELECT "成绩最低",grade.* from grade,
(SELECT grade.stu_subject, MIN(grade.score) as score from grade GROUP BY stu_subject )b
WHERE grade.score = b.score and grade.stu_subject = b.stu_subject;
查询结果如下图所示:
总结:
路漫漫其修远兮,吾将上下而求索。
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!