mysql 存储国过程实现竖表变横表（将行数据值变为字段）

示例：

表结构如下：

CREATE TABLE `pressure` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`presurename` varchar(10) DEFAULT NULL COMMENT '离型剂',
`liquidpressure` varchar(10) DEFAULT NULL COMMENT '液体压力',
`cumulativeflow` varchar(10) DEFAULT NULL COMMENT '累计流量',
`timetraffic` varchar(10) DEFAULT NULL COMMENT '瞬时流量',
`maximumflow` varchar(10) DEFAULT NULL COMMENT '最高流量',
`systime` datetime DEFAULT NULL COMMENT '系统时间',
`sysday` date DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='锻压机内工艺数据记录，离型剂压力流量监控数据保存。';

 

插入数据

INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (7, 'R3上模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (9, 'R3下模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (10, 'R3上模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (11, 'R3下模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (12, 'R5上模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (13, 'R5下模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (14, 'R5上模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (15, 'R5下模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (17, 'R3上模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (18, 'R3下模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (19, 'R3上模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (20, 'R3下模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (21, 'R5上模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (22, 'R5下模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (23, 'R5上模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');
INSERT INTO `pressure`(`id`, `presurename`, `liquidpressure`, `cumulativeflow`, `timetraffic`, `maximumflow`, `systime`, `sysday`) VALUES (24, 'R5下模腔', '2', '3', '4', '4', '2021-11-09 17:07:19', '2021-11-09');

 

 

需求：将表中的pressurename字段的值变为字段，使用存储过程是实现。

 

sql语句如下：

 

CREATE DEFINER=`root`@`%` PROCEDURE `getpressuredays`()
BEGIN


set @day :=7;

Select

group_concat(

DISTINCT

if(liquidpressure is null,

CONCAT('max(if (presurename is null, presurename, 0)) as ''NULL'' '),

CONCAT('max(if (presurename=''', presurename, ''', liquidpressure, ''-'')) as ''',presurename, ''' '))

) into @sql from pressure join (SELECT @sql:='')a;

set @sql = concat('select DATE_FORMAT(sysday,"%Y-%m-%d") as systime, ', @sql, 'from pressure
where DATE_SUB(CURDATE(), INTERVAL "',@day,'" DAY)
<= date(sysday)
group by DATE_FORMAT(sysday,"%Y-%m%d") order by sysday desc;');

PREPARE stmt FROM @sql;

 

EXECUTE stmt;

END

 

验证结果：