加入有一个用户各科成绩的总表，需要查询每个学生的各科成绩；需要查询某一课程学生成绩的排名；需要查询每个学生的总成绩排名；

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;

查询英语课程每位学生成绩的排名

select * from std_course where category = 20 order by score desc;

查询每个学生的总成绩排名

根据user_id分组，计算每个学生的总成绩，并且进行排名。

select `user_id`,group_concat(category),sum(score) from std_course group by user_id order by sum(score) desc;