News Center

列數(shù)據(jù)轉(zhuǎn)換成按行展示的sql該怎么寫(xiě)？

今天朋友問(wèn)到一個(gè)有趣的問(wèn)題，直接把我難住了

下面是我給出的方法 ，這個(gè)不是準(zhǔn)確答案

#新建測(cè)試表，添加數(shù)據(jù)

-- ----------------------------
-- 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');

#硬寫(xiě)查詢

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;

#過(guò)程方法

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;

山西更新科技屌絲程序