mysql行列互转

1.构造常量表

SELECT '优' as label, 'A' as `value`
UNION ALL
SELECT '中' as label, 'B' as `value`
UNION ALL
SELECT '良' as label, 'C' as `value`
UNION ALL
SELECT '差' as label, 'D' as `value`;

2.常量关联其他表（常量必有值）| 统计各成绩人数

用户表

/*
 Navicat Premium Data Transfer

 Source Server         : java505
 Source Server Type    : MySQL
 Source Server Version : 80022
 Source Host           : localhost:3306
 Source Schema         : bise

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

 Date: 31/01/2023 00:03:47
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dept` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sex` tinyint(0) NOT NULL,
  `grade` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', '人事部', 1, 'A');
INSERT INTO `user` VALUES (2, '李四', '人事部', 1, 'B');
INSERT INTO `user` VALUES (3, '王五', '测试部', 2, 'C');
INSERT INTO `user` VALUES (4, '赵六', '测试部', 2, '');
INSERT INTO `user` VALUES (5, '田七', '技术部', 2, 'A');

SET FOREIGN_KEY_CHECKS = 1;

SELECT * FROM user;

WITH temp as (
	SELECT '优' as label, 'A' as `value`
	UNION
	SELECT '中' as label, 'B' as `value`
	UNION
	SELECT '良' as label, 'C' as `value`
	UNION
	SELECT '差' as label, 'D' as `value`
),w1 as (
	SELECT grade,count(id)`count` FROM user GROUP BY `grade`
)

SELECT 
temp.*,
IFNULL(w1.count,0) `count` -- 没有值取0
FROM temp LEFT JOIN w1 ON w1.grade = temp.`value`;

3.行转列 统计部门人数（将行转列,需要知道多少行,当不知道多少行时可以与1常量表关联构造）

3.1 转一行（一维,使用聚合函数String用group concat函数）

WITH w3 as (
	SELECT dept, count(id) `count` FROM user GROUP BY dept
)
-- 3.1 转一行（一维,使用聚合函数String用group concat函数）
	SELECT
  	sum(IF(dept='人事部',	`count`,0)) as rs,
	sum(IF(dept='测试部',	`count`,0)) as cs,
	sum(IF(dept='技术部',	`count`,0)) as js
 	FROM w3 ;

3.2 转一行（二维,使用聚合函数String用group concat函数）

WITH w3 as (
	SELECT dept, count(id) `count` FROM user GROUP BY dept
)
-- 3.2 转一行（二维,使用聚合函数String用group concat函数）
SELECT
GROUP_CONCAT(IF(dept='测试部',	`count`,0)) as rs,
GROUP_CONCAT(IF(dept='测试部',	`count`,0)) as cs,
GROUP_CONCAT(IF(dept='技术部',	`count`,0)) as js
FROM w3 ;

3.3 转n行（二维）

WITH w3 as (
	SELECT dept, count(id) `count` FROM user GROUP BY dept
)
-- 3.3 转n行（二维）
SELECT
 	sum(IF(dept='人事部',	`count`,0)) as rs,
	sum(IF(dept='测试部',	`count`,0)) as cs,
	sum(IF(dept='技术部',	`count`,0)) as js
FROM w3 GROUP BY dept ;

4.列转行（3.3 转 原始的w3）

-- 构造数据
with w4 as (
	WITH w3 as (
		SELECT dept, count(id) `count` FROM user GROUP BY dept
	)
	SELECT
		sum(IF(dept='人事部',	`count`,0)) as rs,
		sum(IF(dept='测试部',	`count`,0)) as cs,
		sum(IF(dept='技术部',	`count`,0)) as js
	FROM w3 GROUP BY dept
)
-- 转化
SELECT '人事部' as dept ,rs `count` FROM w4
UNION ALL
SELECT '测试部' as dept ,cs `count` FROM w4
UNION ALL
SELECT '技术部' as dept ,js `count` FROM w4