IT俱乐部 MySql MySQL行列互换的实现示例

MySQL行列互换的实现示例

场景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俱乐部!

本文收集自网络,不代表IT俱乐部立场,转载请注明出处。https://www.2it.club/database/mysql/12538.html
上一篇
下一篇
联系我们

联系我们

在线咨询: QQ交谈

邮箱: 1120393934@qq.com

工作时间:周一至周五,9:00-17:30,节假日休息

关注微信
微信扫一扫关注我们

微信扫一扫关注我们

返回顶部