mysql
有一个用户各科成绩的总表;
查询每个学生的各科成绩;
查询某一课程学生成绩的排名;
查询每个学生的总成绩排名;
成绩数据表结构
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50726
Source Host : localhost:3306
Source Schema : test_db
Target Server Type : MySQL
Target Server Version : 50726
File Encoding : 65001
Date: 06/12/2021 22:08:27
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for std_student
-- ----------------------------
DROP TABLE IF EXISTS `std_student`;
CREATE TABLE `std_student` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 101 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学生' ROW_FORMAT = Fixed;
-- ----------------------------
-- Records of std_student
-- ----------------------------
INSERT INTO `std_student` VALUES (20, '张晓');
INSERT INTO `std_student` VALUES (50, '李菲');
INSERT INTO `std_student` VALUES (100, '胡博');
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Table structure for std_course
-- ----------------------------
DROP TABLE IF EXISTS `std_course`;
CREATE TABLE `std_course` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_id` int(10) NULL DEFAULT NULL COMMENT 'user_id',
`category` int(10) NULL DEFAULT NULL COMMENT '科目分类',
`score` decimal(10, 1) NULL DEFAULT NULL COMMENT '得分',
`create_time` int(10) NULL DEFAULT NULL COMMENT '创建时间',
`update_time` int(10) NULL DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 34 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '分数表' ROW_FORMAT = Fixed;
-- ----------------------------
-- Records of std_course
-- ----------------------------
INSERT INTO `std_course` VALUES (10, 100, 12, 88.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (11, 100, 11, 90.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (12, 100, 10, 85.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (13, 100, 13, 80.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (14, 100, 15, 99.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (15, 100, 16, 92.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (16, 100, 17, 105.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (17, 100, 17, 115.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (18, 50, 12, 80.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (19, 50, 11, 98.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (20, 50, 10, 75.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (21, 50, 13, 86.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (22, 50, 15, 86.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (23, 50, 16, 82.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (24, 50, 17, 115.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (25, 50, 17, 100.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (26, 20, 12, 80.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (27, 20, 11, 88.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (28, 20, 10, 79.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (29, 20, 13, 86.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (30, 20, 15, 80.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (31, 20, 16, 92.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (32, 20, 17, 112.0, 1575542486, 1575542486);
INSERT INTO `std_course` VALUES (33, 20, 17, 90.0, 1575542486, 1575542486);
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Table structure for std_category
-- ----------------------------
DROP TABLE IF EXISTS `std_category`;
CREATE TABLE `std_category` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` char(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名称',
`status` tinyint(1) NULL DEFAULT NULL COMMENT '状态',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '课程类别' ROW_FORMAT = Fixed;
-- ----------------------------
-- Records of std_category
-- ----------------------------
INSERT INTO `std_category` VALUES (10, '历史', 1);
INSERT INTO `std_category` VALUES (11, '政治', 1);
INSERT INTO `std_category` VALUES (12, '物理', 1);
INSERT INTO `std_category` VALUES (13, '化学', 1);
INSERT INTO `std_category` VALUES (15, '数学', 1);
INSERT INTO `std_category` VALUES (16, '语文', 1);
INSERT INTO `std_category` VALUES (17, '英语', 1);
SET FOREIGN_KEY_CHECKS = 1;
查询张晓学生的各科成绩
select * from std_course where user_id = 20 order by score desc;
id | user_id | category | score | create_time | update_time |
---|---|---|---|---|---|
32 | 20 | 17 | 112.0 | 1575542486 | 1575542486 |
31 | 20 | 16 | 92.0 | 1575542486 | 1575542486 |
33 | 20 | 17 | 90.0 | 1575542486 | 1575542486 |
27 | 20 | 11 | 88.0 | 1575542486 | 1575542486 |
29 | 20 | 13 | 86.0 | 1575542486 | 1575542486 |
26 | 20 | 12 | 80.0 | 1575542486 | 1575542486 |
30 | 20 | 15 | 80.0 | 1575542486 | 1575542486 |
28 | 20 | 10 | 79.0 | 1575542486 | 1575542486 |
查询英语课程每位学生成绩的排名
select * from std_course where category = 20 order by score desc;
id | user_id | category | score | create_time | update_time |
---|---|---|---|---|---|
17 | 100 | 17 | 115.0 | 1575542486 | 1575542486 |
24 | 50 | 17 | 115.0 | 1575542486 | 1575542486 |
32 | 20 | 17 | 112.0 | 1575542486 | 1575542486 |
16 | 100 | 17 | 105.0 | 1575542486 | 1575542486 |
25 | 50 | 17 | 100.0 | 1575542486 | 1575542486 |
33 | 20 | 17 | 90.0 | 1575542486 | 1575542486 |
查询每个学生的总成绩排名
根据user_id分组,计算每个学生的总成绩,并且进行排名。
select `user_id`,group_concat(category),sum(score) from std_course group by user_id order by sum(score) desc;
user_id | group_concat(category) | sum(score) |
---|---|---|
100 | 12,11,10,13,15,16,17,17 | 754.0 |
50 | 12,11,10,13,15,16,17,17 | 722.0 |
20 | 12,11,10,13,15,16,17,17 | 707.0 |
发表评论 取消回复