mysql行转换为列
时间：2021-07-01 10:21:17
帮助过：12人阅读
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`;
mysql行转换为列
标签：结果 images group by ble 显示 ase blog key bsp
