场景1 行转换列
1、表结构和数据
/*
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
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 score
group by stu_no;
场景2：列转换行
1、准备数据表结构和数据
/*
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、业务代码
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
) t
order by t.name;
到此这篇关于MySQL行列互换的实现示例的文章就介绍到这了,更多相关MySQL行列互换内容请搜索IT俱乐部以前的文章或继续浏览下面的相关文章希望大家以后多多支持IT俱乐部！
