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