从如下数据(在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/

10-09 20:10
查看更多