MySQL如何更改表中的列和行？

Question

我有下一张 table :

date            3 6 9 12 15 18 21 24     
2015.01.01      3 2 5  2  5  7  2  4
2015.01.02      9 3 1  3  4  6  4  1
2015.01.03      0 2 3  1  5  6  3  4

我需要获取如下表格:

date                  time          numbers
2015.01.01            03:00:00       3
2015.01.01            06:00:00       2
2015.01.01            09:00:00       5
2015.01.01            12:00:00       2
2015.01.01            15:00:00       5
2015.01.01            18:00:00       7
2015.01.01            21:00:00       2
2015.01.01            24:00:00       4
2015.01.02            03:00:00       9
2015.01.02            06:00:00       3

我该怎么做？

Answer 1

这是union all的一种方法:

select `date`, '3:00:00' as `time`, `3` as val from yourtable
union all select `date`, '6:00:00' as `time`, `6` as val from yourtable
union all select `date`, '9:00:00' as `time`, `9` as val from yourtable
...

• SQL Fiddle Demo

顺便说一句，我强烈建议重命名您的列...

<小时/>

这是使用条件聚合和交叉连接的另一种方法:

select `date`, val as `time`, 
  max(case when val = 3 then `3`
           when val = 6 then `6`
           when val = 9 then `9`
      end) as `val`
from yourtable cross join (select 3 val union all select 6 union all select 9) t
group by `date`, val

• More Fiddle