DROP TABLE IF EXISTS `tt`; CREATE TABLE `tt` ( `id` int(11) NOT NULL, `filed_name` varchar(30) DEFAULT NULL, `val` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of tt -- ---------------------------- INSERT INTO `tt` VALUES ('1', 'A', '11'); INSERT INTO `tt` VALUES ('1', 'B', '22'); INSERT INTO `tt` VALUES ('1', 'C', '33'); INSERT INTO `tt` VALUES ('2', 'A', '44'); INSERT INTO `tt` VALUES ('2', 'B', '55'); INSERT INTO `tt` VALUES ('2', 'C', '66');
select id, max(A) A, max(B) B, max(C) C from( select id, case filed_name when 'A' THEN val else 0 end A, case filed_name when 'B' THEN val else 0 end B, case filed_name when 'C' THEN val else 0 end C FROM tt ) t2 group by id
select id, MAX(case filed_name when 'A' THEN val ELSE 0 END ) AS 'A', MAX(case filed_name when 'B' THEN val ELSE 0 END ) AS 'B', MAX(case filed_name when 'C' THEN val ELSE 0 END ) AS 'C' FROM tt group by id
select id ,( SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(case filed_name when ''',t.filed_name,''' THEN val ELSE 0 END ) AS ''',t.filed_name, '''' ) ) FROM tt t ) FROM tt
结果: