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