mysql行转换为列
标签：结果 images group by ble 显示 ase blog key bsp
mysql行转换为列
TABLE `t_log_code_num` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`server_id` int(3) NOT NULL,
`date` date NOT NULL,
`code` varchar(20) NOT NULL,
`num` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO t_log_code_num (`server_id`, `date`, `code`, `num`) VALUES (‘1‘, ‘2017-07-31‘, ‘201058‘, ‘1‘);
INSERT INTO t_log_code_num (`server_id`, `date`, `code`, `num`) VALUES (‘2‘, ‘2017-07-31‘, ‘201612‘, ‘2113‘);
INSERT INTO t_log_code_num (`server_id`, `date`, `code`, `num`) VALUES (‘1‘, ‘2017-07-30‘, ‘201058‘, ‘1‘);
INSERT INTO t_log_code_num (`server_id`, `date`, `code`, `num`) VALUES (‘2‘, ‘2017-07-30‘, ‘201314‘, ‘310‘);
INSERT INTO t_log_code_num (`server_id`, `date`, `code`, `num`) VALUES (‘1‘, ‘2017-07-29‘, ‘201322‘, ‘1890‘);
INSERT INTO t_log_code_num (`server_id`, `date`, `code`, `num`) VALUES (‘2‘, ‘2017-07-29‘, ‘201203‘, ‘379‘);
SELECT * from t_log_code_num;
select t.`code`,
sum(case t.date when ‘2017-07-29‘ then t.num else 0 end ) as ‘2017-07-29‘,
sum(case t.date when ‘2017-07-30‘ then t.num else 0 end ) as ‘2017-07-30‘,
sum(case t.date when ‘2017-07-31‘ then t.num else 0 end ) as ‘2017-07-31‘
from (
select date,code, sum(num) num from t_log_code_num GROUP BY date,code
) t GROUP BY t.`code`;