知道了,还是需要自己多动手啊! select objectid,to_char(date,'yyyy-mm-dd') date, sum(data) data from test1 group by objectid,to_char(date,'yyyy-mm-dd')
select objectid,to_char(date,'yyyy-mm-dd'),sum(data) from test1 group by objectid,to_char(date,'yyyy-mm-dd'); 应该是这样 但是没有测试,请楼主自己测一下!
select objectid,to_char(date,'yyyy=mm-dd'),sum(data) from tab group by objectid,to_char(date,'yyyy=mm-dd')
with test1 as( select 1 objectid,to_date('2010-01-01','yyyy-mm-dd hh24:mi:ss') date1,100 data from dual union all select 1 objectid,to_date('2010-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') date1,200 data from dual union all select 1 objectid,to_date('2010-01-01 02:00:00','yyyy-mm-dd hh24:mi:ss') date1,300 data from dual union all select 2 objectid,to_date('2010-01-01','yyyy-mm-dd hh24:mi:ss') date1,300 data from dual union all select 2 objectid,to_date('2010-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') date1,400 data from dual union all select 2 objectid,to_date('2010-01-01 02:00:00','yyyy-mm-dd hh24:mi:ss') date1,500 data from dual ) select objectid,to_char(date1,'yyyy-mm-dd'),sum(data) from test1 group by objectid,to_char(date1,'yyyy-mm-dd')结果: 1 2010-01-01 600 2 2010-01-01 1200
还有一个问题,怎么得到每月的统计呢,如下,date格式是YYYY-MM-01 objectid date data 1 2010-01-01 1800
哦,加个to_char(date,'yyyy-mm')||'-01'
trunc(date,'mm') 用这个最好....
with test1 as( select 1 objectid,to_date('2010-01-01','yyyy-mm-dd hh24:mi:ss') date1,100 data from dual union all select 1 objectid,to_date('2010-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') date1,200 data from dual union all select 1 objectid,to_date('2010-01-01 02:00:00','yyyy-mm-dd hh24:mi:ss') date1,300 data from dual union all select 2 objectid,to_date('2010-01-01','yyyy-mm-dd hh24:mi:ss') date1,300 data from dual union all select 2 objectid,to_date('2010-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') date1,400 data from dual union all select 2 objectid,to_date('2010-01-01 02:00:00','yyyy-mm-dd hh24:mi:ss') date1,500 data from dual ) select objectid,trunc(date1,'mm'),sum(data) from test1 group by objectid,trunc(date1,'mm')
create table test1 ( objectid NUMBER not null, date NUMBER not null, data FLOAT ) 你的这个表结构,date NUMBER not null, 应该是date吧! select objectid,trunc(t.date,'mm'),sum(data) from test1 t group by objectid,trunc(t.date,'mm')
select objectid,to_char(date,'yyyy-mm-dd') date,
sum(data) data from test1
group by objectid,to_char(date,'yyyy-mm-dd')
应该是这样 但是没有测试,请楼主自己测一下!
from tab
group by objectid,to_char(date,'yyyy=mm-dd')
select 1 objectid,to_date('2010-01-01','yyyy-mm-dd hh24:mi:ss') date1,100 data from dual
union all
select 1 objectid,to_date('2010-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') date1,200 data from dual
union all
select 1 objectid,to_date('2010-01-01 02:00:00','yyyy-mm-dd hh24:mi:ss') date1,300 data from dual
union all
select 2 objectid,to_date('2010-01-01','yyyy-mm-dd hh24:mi:ss') date1,300 data from dual
union all
select 2 objectid,to_date('2010-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') date1,400 data from dual
union all
select 2 objectid,to_date('2010-01-01 02:00:00','yyyy-mm-dd hh24:mi:ss') date1,500 data from dual
)
select objectid,to_char(date1,'yyyy-mm-dd'),sum(data) from test1 group by objectid,to_char(date1,'yyyy-mm-dd')结果:
1 2010-01-01 600
2 2010-01-01 1200
objectid date data 1 2010-01-01 1800
with test1 as(
select 1 objectid,to_date('2010-01-01','yyyy-mm-dd hh24:mi:ss') date1,100 data from dual
union all
select 1 objectid,to_date('2010-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') date1,200 data from dual
union all
select 1 objectid,to_date('2010-01-01 02:00:00','yyyy-mm-dd hh24:mi:ss') date1,300 data from dual
union all
select 2 objectid,to_date('2010-01-01','yyyy-mm-dd hh24:mi:ss') date1,300 data from dual
union all
select 2 objectid,to_date('2010-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss') date1,400 data from dual
union all
select 2 objectid,to_date('2010-01-01 02:00:00','yyyy-mm-dd hh24:mi:ss') date1,500 data from dual
)
select objectid,trunc(date1,'mm'),sum(data) from test1 group by objectid,trunc(date1,'mm')
(
objectid NUMBER not null,
date NUMBER not null,
data FLOAT
)
你的这个表结构,date NUMBER not null, 应该是date吧!
select objectid,trunc(t.date,'mm'),sum(data) from test1 t group by objectid,trunc(t.date,'mm')