一个表中的Mysql行被移动并合并到命名列中

Question

我有以下数据

drop table if exists states;
create table states (id integer, state varchar(100), content varchar(100));
insert into states (id, state, content) values (1, 'soc', '73');
insert into states (id, state, content) values (2, 'range', '412');
insert into states (id, state, content) values (3, 'range', '410');
insert into states (id, state, content) values (3, 'soc', '71');
insert into states (id, state, content) values (5, 'range', '405');

使用以下语句

SELECT id,
CASE WHEN state = 'range' THEN content ELSE null END AS 'range',
CASE WHEN state = 'soc' THEN content ELSE null END AS 'soc'
FROM states;

结果是

id	范围	soc
1			73
2	412
3		410
3			71
5	405

SELECT id,
CASE WHEN state = 'range' THEN content ELSE null END AS 'range',
CASE WHEN state = 'soc' THEN content ELSE null END AS 'soc'
FROM states;

>3//tr>1//tr>

id	range	soc
1	null
2	412	null
3	410	null
3	空
5	405	null