从如下数据(在MySQL数据库中):
mysql> select * from test;
+------------+--------+
| start | name |
+------------+--------+
| 2013-04-01 | Donald |
| 2013-04-02 | Daisy |
| 2013-04-03 | Mickey |
| 2013-04-03 | Minnie |
| 2013-04-01 | Pluto |
| 2013-04-02 | Goofy |
+------------+--------+
6 rows in set (0.00 sec)
我想编写一个查询来生成如下输出:
2013-04-01 2013-04-02 2013-04003
---------- ---------- ----------
Donald Daisy Mickey
Pluto Goofy Minnie
有人能建议SQL查询生成这个输出吗?
如果有帮助的话,这是我用来创建示例数据的代码。
create table test (
start date,
name varchar (20));
insert into test values ('2013-04-01','Donald');
insert into test values ('2013-04-02','Daisy');
insert into test values ('2013-04-03','Mickey');
insert into test values ('2013-04-03','Minnie');
insert into test values ('2013-04-01','Pluto');
insert into test values ('2013-04-02','Goofy');`
最佳答案
必须有一个透视表或一系列case语句。
Select
Case When Start = '2013-04-01' Then name End As '2013-04-01',
Case When Start = '2013-04-02' Then name End As '2013-04-02',
Case When Start = '2013-04-03' Then name End As '2013-04-03'
From Test;
如果表更复杂的话,动态地生成它会有点困难。只需导出到excel并使用pivot表功能,就可以更轻松地处理更大的数据。
关于mysql - SQL产生所需的输出,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17713937/