sql在navicat中进行分组查询

MySQL / 224人浏览 / 0人评论

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

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;
iduser_idcategoryscorecreate_timeupdate_time
322017112.015755424861575542486
31201692.015755424861575542486
33201790.015755424861575542486
27201188.015755424861575542486
29201386.015755424861575542486
26201280.015755424861575542486
30201580.015755424861575542486
28201079.015755424861575542486

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

select * from std_course where category = 20 order by score desc;
iduser_idcategoryscorecreate_timeupdate_time
1710017115.015755424861575542486
245017115.015755424861575542486
322017112.015755424861575542486
1610017105.015755424861575542486
255017100.015755424861575542486
33201790.015755424861575542486

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

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

select `user_id`,group_concat(category),sum(score) from std_course group by user_id order by sum(score) desc;
user_idgroup_concat(category)sum(score)
10012,11,10,13,15,16,17,17754.0
5012,11,10,13,15,16,17,17722.0
2012,11,10,13,15,16,17,17707.0

转载注明:

扩展查找

0 条评论

还没有人发表评论

发表评论 取消回复

记住我的信息,方便下次评论
有人回复时邮件通知我