MySQL 列变行 数据变换 行列转换

/*创建表*/
DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `field_name` varchar(32) NOT NULL COMMENT '字段名称',
  `field_val` text NOT NULL COMMENT '字段值',
  `key`  varchar(32) NOT NULL COMMENT '分组标示',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

/*数据*/
INSERT INTO `demo` VALUES ('1', 'date', '2015-01-01 00:00:00',1);
INSERT INTO `demo` VALUES ('2', 'createtime', '2015-12-30 10:12:12',1);
INSERT INTO `demo` VALUES ('3', 'date', '2015-01-01 00:00:10',2);
INSERT INTO `demo` VALUES ('4', 'createtime', '2015-12-30 12:12:12',2);

/*创建视图*/
DROP VIEW IF EXISTS `demo_view`;
create view demo_view as SELECT
`key`,
	MAX(
		CASE
		WHEN field_name = 'date' THEN
			field_val
		END
	) AS date,
	MAX(
		CASE
		WHEN field_name = 'createtime' THEN
			field_val
		END
	) AS createtime
FROM
	demo
GROUP BY `key`;

select * from demo_view ;

/*不使用视图，直接搜索*/
SELECT
`key`,
	MAX(
		CASE
		WHEN field_name = 'date' THEN
			field_val
		END
	) AS date,
	MAX(
		CASE
		WHEN field_name = 'createtime' THEN
			field_val
		END
	) AS createtime
FROM
	demo
GROUP BY `key`;