学生课程行转列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | -- 同一张表里 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 ; |
同一张表里的数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 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; |
不同的表里的数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | /* 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; |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】博客园携手 AI 驱动开发工具商 Chat2DB 推出联合终身会员
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 用纯.NET开发并制作一个智能桌面机器人:从.NET IoT入门开始
· 一个超经典 WinForm,WPF 卡死问题的终极反思
· ASP.NET Core - 日志记录系统(二)
· .NET 依赖注入中的 Captive Dependency
· .NET Core 对象分配(Alloc)底层原理浅谈
· 开箱你的 AI 语音女友「GitHub 热点速览」
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(二):用.NET IoT库
· C#钩子(Hook) 捕获键盘鼠标所有事件 - 5分钟没有操作,自动关闭 Form 窗体
· 特斯拉CEO埃隆.马斯克的五步工作法,怎么提高工程效率加速产品开发?
· 几个自学项目的通病,别因为它们浪费了时间!