News Center

列数据转换成按行展示的sql该怎么写？

今天朋友问到一个有趣的问题，直接把我难住了

下面是我给出的方法 ，这个不是准确答案

#新建测试表，添加数据

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`f1` varchar(255) DEFAULT NULL,
`f2` varchar(255) DEFAULT NULL,
`f3` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', '字段1', '字段2', '字段3');
INSERT INTO `test` VALUES ('2', '字段11', '字段22', '字段33');

#硬写查询

select col, sum(ifnull(c1,0)) as c1,sum(ifnull(c2,0)) as c2 from (
select col, case id when 1 then val end AS c1,case id when 2 then val end AS c2 from(
select 'f1' as col, f1 as val, id from test
union all
select 'f2' as col, f2 as val, id from test
union all
select 'f3' as col, f3 as val, id from test
)t order by col)s group by col;

#过程方法

SET @AA='';
SET @BB='';
SET @CC='';
SET @str_aa='';
SET @str_bb='';
SET @str_cc='';
SELECT @AA:=CONCAT(@AA,'sum(ifnull(c',id,',0)) as c',id,',') as aa into @str_aa FROM (SELECT DISTINCT id FROM test) A order by length(aa) desc limit 1;
SELECT @BB:=CONCAT(@BB,'case id when ',id,' then val end as c',id,',') as bb into @str_bb FROM (SELECT DISTINCT id FROM test) B order by length(bb) desc limit 1;
SELECT @CC:=CONCAT(@CC,'select \'',col,'\' as col, ',col,' as val, id from test union all ') as cc into @str_cc FROM (SELECT COLUMN_NAME as col FROM information_schema.columns WHERE table_name='test' HAVING col!='id') C order by length(cc) desc limit 1;

SET @DD=CONCAT('select col,',LEFT(@str_aa,char_length(@str_aa)-1),' from (select col,',LEFT(@str_bb,char_length(@str_bb)-1),' from(',LEFT(@str_cc,char_length(@str_cc)-10),')t order by col)s group by col');
PREPARE stmt FROM @DD;
EXECUTE stmt ;
deallocate prepare stmt;

山西更新科技屌丝程序