本来有这么一个表
Date CountLog CountIndividual
02-Jun-06 5 1
03-Jun-06 11 2
.....
...
.
12-Dec-06 23 5我想修改成这样的结构
Day JunCountLog JunCountInd JulCountLog JulCountInd SepCountLog......
1 5 1 23 3 15 ....
2 .............................................
3 ......................
..
31 ....在建立新表结构以后,请问我如何把数据从第一个表转移到第二个表。请给出相关的SQL语句,谢谢!!!
Date CountLog CountIndividual
02-Jun-06 5 1
03-Jun-06 11 2
.....
...
.
12-Dec-06 23 5我想修改成这样的结构
Day JunCountLog JunCountInd JulCountLog JulCountInd SepCountLog......
1 5 1 23 3 15 ....
2 .............................................
3 ......................
..
31 ....在建立新表结构以后,请问我如何把数据从第一个表转移到第二个表。请给出相关的SQL语句,谢谢!!!
selelct date,sum(JunCountLog) as JunCountLog, sum(JunCountInd) as JunCountInd
sum(JulCountLog) as JulCountLog, sum(JulCountInd) as JulCountInd
...
from
(
select to_number(to_char(date,'dd')) as Day,
decode(to_char(date,'mm'),'06',CountLog ,0) as JunCountLog ,
decode(to_char(date,'mm'),'06',CountIndividual ,0) as JunCountInd ,
decode(to_char(date,'mm'),'07',CountLog ,0) as JulCountLog ,
decode(to_char(date,'mm'),'06',CountIndividual ,0) as JulCountLog ,
...
from table
)
group by date
selelct Day,sum(JunCountLog) as JunCountLog, sum(JunCountInd) as JunCountInd
sum(JulCountLog) as JulCountLog, sum(JulCountInd) as JulCountInd
...
from
(
select to_number(to_char(date,'dd')) as Day,
decode(to_char(date,'mm'),'06',CountLog ,0) as JunCountLog ,
decode(to_char(date,'mm'),'06',CountIndividual ,0) as JunCountInd ,
decode(to_char(date,'mm'),'07',CountLog ,0) as JulCountLog ,
decode(to_char(date,'mm'),'06',CountIndividual ,0) as JulCountLog ,
...
from table
)
group by date
如
alter table 表 add column 列名 varchar2(20)删除一个字段:
alter table 表 drop column 列名
数据转移要在删除列前完成,如果还觉得不保险,可以用到临时表放数据
decode的目的是先把每行仅有的几列分解为按月份区分的多列,符合的列取本月值,不符合本行信息的列取值为0
decode()过滤后的结果集:
Day JunCountLog JunCountInd JulCountLog JulCountInd SepCountLog......
6 5 1 0 0 0 ....
6 0 0 23 3 0 ...
7 ......................
.....
31 ....
求和是为了把本来应该在一行但是被分在了多行的值合并在一行上,如从1月1日到12月1日都有记录,那么上一流程会生成12行信息,但每行只有对应月份字段有值其他字段为0,分组求和后也就把12个月所有1号行都合成了一行,而且每个月的值都是准确的,如:
求和后结果
Day JunCountLog JunCountInd JulCountLog JulCountInd SepCountLog......
6 5 1 23 3 0 ....
7 ......................
.....
31 ....
如果再不理解,建议找本离散数学或者算法方面的基础书籍去看一看