mysql 单列转换为行,mysql行转换为列

1.建表

CREATE 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;

2.录入数据

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

3.数据显示

4.统计sql

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`;

5.统计结果

代码如下：

CREATE TABLE`t_log_code_num` (

`id`int(11) NOT NULLAUTO_INCREMENT,

`server_id`int(3) NOT NULL,

`date` dateNOT 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 * fromt_log_code_num;selectt.`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 BYdate,code

) tGROUP BY t.`code`;

原文：http://www.cnblogs.com/zhouj/p/7269930.html