行转列，列转行

最新推荐文章于 2024-05-11 09:52:18 发布

刀下阳光

最新推荐文章于 2024-05-11 09:52:18 发布

阅读量413

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/upordown6263/article/details/126931712

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

引用：https://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

行转列一般通过CASE WHEN 语句来实现：

举例：学生成绩表实现行转列

CREATE TABLE `student_scores` (
  `user_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学生姓名',
  `subject` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '科目',
  `score` decimal(5,2) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO student_scores SELECT 'Nick', '语文', 80;
 
INSERT INTO student_scores SELECT 'Nick', '数学', 90;
 
INSERT INTO student_scores SELECT 'Nick', '英语', 70;
 
INSERT INTO student_scores SELECT 'Nick', '生物', 85;
 
INSERT INTO student_scores SELECT 'Kent', '语文', 80;
 
INSERT INTO student_scores SELECT 'Kent', '数学', 90;
 
INSERT INTO student_scores SELECT 'Kent', '英语', 70;
 
INSERT INTO student_scores SELECT 'Kent', '生物', 85;

如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据

SELECT 
      user_name, 
      MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
      MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
      MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
      MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
FROM student_scores
GROUP BY user_name

查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了
在这里插入图片描述

列转行，主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表progrectdetail

CREATE TABLE `progrectdetail` (
  `ProgrectName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `OverseaSupply` int(255) DEFAULT NULL,
  `NativeSupply` int(255) DEFAULT NULL,
  `SouthSupply` int(255) DEFAULT NULL,
  `NorthSupply` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO ProgrectDetail
SELECT 'A', 100, 200, 50, 50
UNION ALL
SELECT 'B', 200, 300, 150, 150
UNION ALL
SELECT 'C', 159, 400, 20, 320
UNION ALL
SELECT 'D', 250, 30, 15, 15

我们可以通过下面的脚本来实现，查询结果如下图所示：

SELECT ProgrectName,'OverseaSupply' AS Supplier,MAX( OverseaSupply ) AS 'SupplyNum' FROM ProgrectDetail GROUP BY ProgrectName 
UNION ALL
SELECT ProgrectName,'NativeSupply' AS Supplier,MAX( NativeSupply ) AS 'SupplyNum' FROM ProgrectDetail GROUP BY ProgrectName 
UNION ALL
SELECT ProgrectName,'SouthSupply' AS Supplier,MAX( SouthSupply ) AS 'SupplyNum' FROM ProgrectDetail GROUP BY ProgrectName 
UNION ALL
SELECT ProgrectName,'NorthSupply' AS Supplier,MAX( NorthSupply ) AS 'SupplyNum' FROM ProgrectDetail GROUP BY ProgrectName