场景1 行转换列
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 | /* Navicat Premium Data Transfer Source Server : 本地 Source Server Type : MySQL Source Server Version : 80027 Source Host : localhost:3306 Source Schema : school Target Server Type : MySQL Target Server Version : 80027 File Encoding : 65001 Date: 13/06/2024 14:50:51*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for score-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` ( `stu_no` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号', `course_no` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程号', `score_prize` decimal(4, 1) NULL DEFAULT NULL COMMENT '成绩', PRIMARY KEY (`stu_no`, `course_no`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of score-- ----------------------------INSERT INTO `score` VALUES ('0001', '0001', 40.0);INSERT INTO `score` VALUES ('0001', '0002', 50.0);INSERT INTO `score` VALUES ('0001', '0003', 48.0);INSERT INTO `score` VALUES ('0002', '0001', 40.0);INSERT INTO `score` VALUES ('0002', '0002', 30.0);INSERT INTO `score` VALUES ('0002', '0003', 99.0);INSERT INTO `score` VALUES ('0003', '0001', 70.0);INSERT INTO `score` VALUES ('0003', '0002', 77.0);INSERT INTO `score` VALUES ('0003', '0003', 60.0);SET FOREIGN_KEY_CHECKS = 1; |
2、效果图说明，第一列用户信息stu_no,第二列课程号course_no,第三列课程成绩
| stu_no | 语文 | 数学 | 英语 |
|---|---|---|---|
| 0001 | 40.0 | 50.0 | 48.0 |
| 0002 | 40.0 | 30.0 | 99.0 |
| 0003 | 70.0 | 77.0 | 60.0 |
3、实现SQL
1 2 3 4 5 6 | select stu_no, sum(IF(course_no = '0001', score_prize, 0)) as '语文', sum(IF(course_no = '0002', score_prize, 0)) as '数学', sum(IF(course_no = '0003', score_prize, 0)) as '英语'from scoregroup by stu_no; |
场景2：列转换行
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 | /* Navicat Premium Data Transfer Source Server : 本地 Source Server Type : MySQL Source Server Version : 80027 Source Host : localhost:3306 Source Schema : school Target Server Type : MySQL Target Server Version : 80027 File Encoding : 65001 Date: 13/06/2024 14:54:37*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for cjs-- ----------------------------DROP TABLE IF EXISTS `cjs`;CREATE TABLE `cjs` ( `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `chinese` int NULL DEFAULT NULL, `math` int NULL DEFAULT NULL, `phy` int NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of cjs-- ----------------------------INSERT INTO `cjs` VALUES ('张三', 89, 90, 79);INSERT INTO `cjs` VALUES ('李四', 88, 79, 90);SET FOREIGN_KEY_CHECKS = 1; |
2、效果图
| name | course |
|---|---|
| 张三 | 90 |
| 张三 | 89 |
| 张三 | 79 |
| 李四 | 79 |
| 李四 | 88 |
| 李四 | 90 |
3、业务代码
1 2 3 4 5 6 7 8 9 10 11 12 | select *from ( select name, math as course from cjs union all select name, chinese as course from cjs union all select name, phy as course from cjs ) torder by t.name; |
到此这篇关于MySQL行列互换的实现示例的文章就介绍到这了,更多相关MySQL行列互换内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！
