学生课程行转列

-- 同一张表里

SELECT user_name ,
    MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM test_tb_grade
GROUP BY USER_NAME;


-- 不同的数据表里

SELECT
    st.stu_id '学号',
    st.stu_name '姓名',
    sum(
        CASE co.course_name
        WHEN '大学语文' THEN
            sc.scores
        ELSE
            0
        END
    ) '大学语文',
    sum(
        CASE co.course_name
        WHEN '新视野英语' THEN
            sc.scores
        ELSE
            0
        END
    ) '新视野英语',
    sum(
        CASE co.course_name
        WHEN '离散数学' THEN
            sc.scores
        ELSE
            0
        END
    ) '离散数学',
    sum(
        CASE co.course_name
        WHEN '概率论与数理统计' THEN
            sc.scores
        ELSE
            0
        END
    ) '概率论与数理统计',
    sum(
        CASE co.course_name
        WHEN '线性代数' THEN
            sc.scores
        ELSE
            0
        END
    ) '线性代数',
    sum(
        CASE co.course_name
        WHEN '高等数学' THEN
            sc.scores
        ELSE
            0
        END
    ) '高等数学'
FROM
    edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
    st.stu_id
ORDER BY
    NULL;

　　

同一张表里的数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test_tb_grade
-- ----------------------------
DROP TABLE IF EXISTS `test_tb_grade`;
CREATE TABLE `test_tb_grade`  (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `COURSE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `SCORE` float NULL DEFAULT 0,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `ucs`(`USER_NAME`, `COURSE`, `SCORE`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test_tb_grade
-- ----------------------------
INSERT INTO `test_tb_grade` VALUES (1, '张三', '数学', 34);
INSERT INTO `test_tb_grade` VALUES (3, '张三', '英语', 58);
INSERT INTO `test_tb_grade` VALUES (2, '张三', '语文', 58);
INSERT INTO `test_tb_grade` VALUES (4, '李四', '数学', 45);
INSERT INTO `test_tb_grade` VALUES (6, '李四', '英语', 45);
INSERT INTO `test_tb_grade` VALUES (5, '李四', '语文', 87);
INSERT INTO `test_tb_grade` VALUES (7, '王五', '数学', 76);
INSERT INTO `test_tb_grade` VALUES (9, '王五', '英语', 89);
INSERT INTO `test_tb_grade` VALUES (8, '王五', '语文', 34);

SET FOREIGN_KEY_CHECKS = 1;

　　

不同的表里的数据

/*
 Navicat Premium Data Transfer

 Source Server         : 本地服务
 Source Server Type    : MySQL
 Source Server Version : 80012
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80012
 File Encoding         : 65001

 Date: 15/12/2021 14:21:40
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for edu_courses
-- ----------------------------
DROP TABLE IF EXISTS `edu_courses`;
CREATE TABLE `edu_courses`  (
  `course_no` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程编号',
  `course_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程名称',
  PRIMARY KEY (`course_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of edu_courses
-- ----------------------------
INSERT INTO `edu_courses` VALUES ('C001', '大学语文');
INSERT INTO `edu_courses` VALUES ('C002', '新视野英语');
INSERT INTO `edu_courses` VALUES ('C003', '离散数学');
INSERT INTO `edu_courses` VALUES ('C004', '概率论与数理统计');
INSERT INTO `edu_courses` VALUES ('C005', '线性代数');
INSERT INTO `edu_courses` VALUES ('C006', '高等数学');

-- ----------------------------
-- Table structure for edu_score
-- ----------------------------
DROP TABLE IF EXISTS `edu_score`;
CREATE TABLE `edu_score`  (
  `stu_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `course_no` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程编号',
  `scores` float NULL DEFAULT NULL COMMENT '得分',
  PRIMARY KEY (`stu_id`, `course_no`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成绩表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of edu_score
-- ----------------------------
INSERT INTO `edu_score` VALUES ('1001', 'C001', 67);
INSERT INTO `edu_score` VALUES ('1001', 'C002', 87);
INSERT INTO `edu_score` VALUES ('1001', 'C003', 83);
INSERT INTO `edu_score` VALUES ('1001', 'C004', 88);
INSERT INTO `edu_score` VALUES ('1001', 'C005', 77);
INSERT INTO `edu_score` VALUES ('1002', 'C001', 68);
INSERT INTO `edu_score` VALUES ('1002', 'C002', 88);
INSERT INTO `edu_score` VALUES ('1002', 'C003', 84);
INSERT INTO `edu_score` VALUES ('1002', 'C004', 89);
INSERT INTO `edu_score` VALUES ('1002', 'C005', 78);
INSERT INTO `edu_score` VALUES ('1003', 'C001', 69);
INSERT INTO `edu_score` VALUES ('1003', 'C002', 89);
INSERT INTO `edu_score` VALUES ('1003', 'C003', 85);
INSERT INTO `edu_score` VALUES ('1003', 'C004', 90);
INSERT INTO `edu_score` VALUES ('1003', 'C005', 79);
INSERT INTO `edu_score` VALUES ('1004', 'C001', 70);
INSERT INTO `edu_score` VALUES ('1004', 'C002', 90);
INSERT INTO `edu_score` VALUES ('1004', 'C003', 86);
INSERT INTO `edu_score` VALUES ('1004', 'C004', 91);
INSERT INTO `edu_score` VALUES ('1005', 'C001', 71);
INSERT INTO `edu_score` VALUES ('1005', 'C002', 91);
INSERT INTO `edu_score` VALUES ('1005', 'C003', 87);
INSERT INTO `edu_score` VALUES ('1005', 'C004', 92);
INSERT INTO `edu_score` VALUES ('1006', 'C001', 72);
INSERT INTO `edu_score` VALUES ('1006', 'C002', 92);
INSERT INTO `edu_score` VALUES ('1006', 'C003', 88);
INSERT INTO `edu_score` VALUES ('1006', 'C004', 93);

-- ----------------------------
-- Table structure for edu_student
-- ----------------------------
DROP TABLE IF EXISTS `edu_student`;
CREATE TABLE `edu_student`  (
  `stu_id` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号',
  `stu_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  PRIMARY KEY (`stu_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of edu_student
-- ----------------------------
INSERT INTO `edu_student` VALUES ('1001', '盲僧');
INSERT INTO `edu_student` VALUES ('1002', '赵信');
INSERT INTO `edu_student` VALUES ('1003', '皇子');
INSERT INTO `edu_student` VALUES ('1004', '寒冰');
INSERT INTO `edu_student` VALUES ('1005', '蛮王');
INSERT INTO `edu_student` VALUES ('1006', '狐狸');

SET FOREIGN_KEY_CHECKS = 1;