在要求输入邮箱的文本域,请填写真实的邮件地址。非真实邮件地址,将收不到回复信息。

Mysql脚本实现行转列

MySql 清风 39℃ 0评论

背景

某项目数据统计,为了更加直观显示一些数据,刚好有这个行转列的一个需求。行转列的需求其实在平常业务中也是比较常见的,在数据统计中使用的比较频繁。行转列以前也遇到过,之前数据库使用的是Microsoft SQL Server。目前使用的是MySQL数据库,这里也做一下简单的记录。

实现

结构与数据准备

为了更好的理解行转列,这里准备一张结构简单都表以及数据。


DROP TABLE IF EXISTS `project_completion_rate`;

CREATE TABLE `project_completion_rate` (
  `id` int(8) NOT NULL,
  `project_user` varchar(20) DEFAULT NULL,
  `project_content` varchar(20) DEFAULT NULL,
  `project_rate` int(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


insert  into `project_completion_rate`(`id`,`project_user`,`project_content`,`project_rate`) values 
(1,'张三','机器人设计',80),
(2,'张三','图像识别',70),
(3,'张三','虚拟化技术',90),
(4,'张三','CPU设计',60),
(5,'李四','机器人设计',95),
(6,'李四','图像识别',96),
(7,'李四','虚拟化技术',97),
(8,'李四','CPU设计',99),
(9,'王五','机器人设计',100),
(10,'王五','虚拟化技术',100);

原始数据示例展示

固定行转列

当需要行转列的项比较少且显示项数目无频繁变动的时候,可以使用以下方式实现行转列的需求。如果行转列的项变动频繁或者项比较多,此种方式实现行转列就不适合。



SELECT project_user AS '项目人员'
	    ,Max(CASE  WHEN p.project_content='CPU设计' THEN p.project_rate ELSE 0 END) AS  'CPU设计'
	    ,Max(CASE  WHEN p.project_content='图像识别' THEN p.project_rate ELSE 0 END) AS  '图像识别'
	    ,Max(CASE  WHEN p.project_content='机器人设计' THEN p.project_rate ELSE 0 END) AS  '机器人设计'
         ,Max(CASE  WHEN p.project_content='虚拟化技术' THEN p.project_rate ELSE 0 END) AS  '虚拟化技术'
FROM project_completion_rate p GROUP BY p.project_user;

动态行转列

动态行转列的实现方式,是为了解决行转列中的项变动频繁或者项比较多的时候而出现的。不固定的项的行转列就可以使用以下方式尝试完成。


-- 动态行转列脚本实现
SET @sql = NULL;
SELECT
 GROUP_CONCAT(DISTINCT
  CONCAT(
   'Max(IF(p.project_content = ''',
   p.project_content,
   ''', p.project_rate, 0)) AS ''',
   p.project_content, ''''
  )
 ) INTO @sql
FROM project_completion_rate p;
 
SET @sql = CONCAT('Select project_user AS ''项目人员'',', @sql, 
            ' From project_completion_rate p Group by p.project_user' );
						
PREPARE stmt FROM @sql;  -- 动态生成脚本
EXECUTE stmt;            -- 动态执行脚本
DEALLOCATE PREPARE stmt; -- 释放

最终结果展示

结语

无论是SQL Server,还是MySQL,其实现行转列的方式也是比较多的。其中Mysql可以实现的方式都包括但不仅限于以上内容。

转载请注明:清风博客 » Mysql脚本实现行转列

喜欢 (7)or分享 (0)
支付宝扫码打赏 微信打赏
发表我的评论
取消评论

CAPTCHA Image
Reload Image
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址