有如下表结构:
DROP TABLE IF EXISTS `tab`;
CREATE TABLE `tab` (
`id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
`kind` varchar(255) DEFAULT NULL,
`amount` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
表中数据为
sql语句
select t.user_id,
max(case t.kind when '0801' then t.amount else 0 end )as '0801',
max(case t.kind when '0812' then t.amount else 0 end )as '0812'
from tab t group by t.user_id;
输出结果为: