一个要在MySQL实现的行转列的SQL需求,原始数据形如,
ID DATE KEY VALUE
A 2021-03-15 F1 1
A 2021-03-15 F2 2
B 2021-03-15 F1 3
B 2021-03-15 F2 4
需要转换为,
ID F1 F2
------------
A 1 2
B 3 4
创建测试表,
create table test (
id varchar(15),
c_date date,
key varchar(15),
value int);
插入测试数据,
insert into test values('A','2021-03-15','F1',1);
insert into test values('A','2021-03-15','F2',2);
insert into test values('B','2021-03-15','F1',3);
insert into test values('B','2021-03-15','F2',4);
mysql> select * from test;
+--------+------------+-----------+-------+
| id | c_date | key | value |
+--------+------------+-----------+-------+
| A | 2021-03-15 | F1 | 1 |
| B | 2021-03-15 | F2 | 2 |
| A | 2021-03-15 | F1 | 3 |
| B | 2021-03-15 | F2 | 4 |
+--------+------------+-----------+-------+
方案1,利用group by对id做聚集,通过case ... when和max得到每个id对应的value,注意,
(1) 需要带上else,否则有些字段,会为空。
(2) 因为用了group by,必须用上max或者其他的函数,否则返回结果多余1条,逻辑就错了。
select id,
max(case key when 'F1' then value else 0 end) F1,
max(case key when 'F2' then value else 0 end) F2
from test
where c_date='2021-03-15'
group by id;
+--------+------+------+
| id | F1 | F2 |
+--------+------+------+
| A | 1 | 2 |
| B | 3 | 4 |
+--------+------+------+
方案2,这种方式,会读取多次test表,
select distinct a.id,
(select value from test b where a.id=b.id and b.key='F1') as 'F1',
(select value from test b where a.id=b.id and b.key='F2') as 'F2'
where a.c_date='2021-03-15'
from test a
+--------+------+------+
| id | F1 | F2 |
+--------+------+------+
| A | 1 | 2 |
| B | 3 | 4 |
+--------+------+------+