/*创建表*/
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`;