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
点赞(2) 打赏

评论列表 共有 0 条评论

暂无评论

微信小程序

微信扫一扫体验

立即
投稿

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部