rq sj dat
2012-12-10 14:00:00 0.00
2012-12-10 18:00:00 0.00
2012-12-10 22:00:00 0.00
2012-12-11 02:00:00 0.00
2012-12-11 06:00:00 0.00
2012-12-11 10:00:00 0.00
2012-12-11 14:00:00 0.04
2012-12-11 18:00:00 0.00
2012-12-11 22:00:00 0.00
2012-12-12 02:00:00 0.00
2012-12-12 06:00:00 0.00
2012-12-12 10:00:00 0.00现已经查出以上数据,结果我想用sum(dat)的方式求和,但现在的难点是,我还想按照前一天的14:00:00 到第二天的10:00:00分组 也就是
2012-12-10 14:00:00 0.00
2012-12-10 18:00:00 0.00
2012-12-10 22:00:00 0.00
2012-12-11 02:00:00 0.00
2012-12-11 06:00:00 0.00
2012-12-11 10:00:00 0.00
这个结果sum出dat 另一个组是
2012-12-11 14:00:00 0.04
2012-12-11 18:00:00 0.00
2012-12-11 22:00:00 0.00
2012-12-12 02:00:00 0.00
2012-12-12 06:00:00 0.00
2012-12-12 10:00:00 0.00以上结果出自这个sql
select *
From t_tprptdetail
where to_date(to_char(rq, 'yyyy-MM-DD') || sj, 'yyyy-MM-DD hh24:mi:ss') >=
(to_date('2012-12-11' || '14:00:00', 'yyyy-MM-DD hh24:mi:ss') - 1)
and to_date(to_char(rq, 'yyyy-MM-DD') || sj, 'yyyy-MM-DD hh24:mi:ss') <=
to_date('2012-12-12' || '10:00:00', 'yyyy-MM-DD hh24:mi:ss')
请各位帮忙提示下 万分感谢 在线等.....oraclesql
2012-12-10 14:00:00 0.00
2012-12-10 18:00:00 0.00
2012-12-10 22:00:00 0.00
2012-12-11 02:00:00 0.00
2012-12-11 06:00:00 0.00
2012-12-11 10:00:00 0.00
2012-12-11 14:00:00 0.04
2012-12-11 18:00:00 0.00
2012-12-11 22:00:00 0.00
2012-12-12 02:00:00 0.00
2012-12-12 06:00:00 0.00
2012-12-12 10:00:00 0.00现已经查出以上数据,结果我想用sum(dat)的方式求和,但现在的难点是,我还想按照前一天的14:00:00 到第二天的10:00:00分组 也就是
2012-12-10 14:00:00 0.00
2012-12-10 18:00:00 0.00
2012-12-10 22:00:00 0.00
2012-12-11 02:00:00 0.00
2012-12-11 06:00:00 0.00
2012-12-11 10:00:00 0.00
这个结果sum出dat 另一个组是
2012-12-11 14:00:00 0.04
2012-12-11 18:00:00 0.00
2012-12-11 22:00:00 0.00
2012-12-12 02:00:00 0.00
2012-12-12 06:00:00 0.00
2012-12-12 10:00:00 0.00以上结果出自这个sql
select *
From t_tprptdetail
where to_date(to_char(rq, 'yyyy-MM-DD') || sj, 'yyyy-MM-DD hh24:mi:ss') >=
(to_date('2012-12-11' || '14:00:00', 'yyyy-MM-DD hh24:mi:ss') - 1)
and to_date(to_char(rq, 'yyyy-MM-DD') || sj, 'yyyy-MM-DD hh24:mi:ss') <=
to_date('2012-12-12' || '10:00:00', 'yyyy-MM-DD hh24:mi:ss')
请各位帮忙提示下 万分感谢 在线等.....oraclesql
解决方案 »
- 谁能提供本关于Oracle方面的基础入门书籍
- ORCLE 服务器的访问
- sql developer中能正常备份导出表,在cmd命令模式下就报错误12560
- 我用Intelligent converters 连接Oracle数据库时一直报错
- 如何登录SQL plus?
- 一个小问题!请高人指点一下,不盛感谢,在线等候(立即给分^_^^_^^_^)!
- 求救 PowerBuilder
- 怎么限制“冗余信息”的输出……
- 有类似substr的用于number型数据的函数吗?
- win10 下安装oracle 11g后,SQL Plus出现乱码问题
- 求一个Function,用 0~9,A~F 这几个数字及字母,产生流水号,请大侠们帮助,急急急
- 求助:表查询
with t1 as
(
select '2012-12-10' rq,'00:00:00' sj,'0.01' dat from dual union all
select '2012-12-10' rq,'08:00:00' sj,'0.00' dat from dual union all
select '2012-12-10' rq,'18:00:00' sj,'0.00' dat from dual union all
select '2012-12-11' rq,'02:00:00' sj,'0.04' dat from dual union all
select '2012-12-11' rq,'10:00:00' sj,'0.00' dat from dual union all
select '2012-12-11' rq,'14:00:00' sj,'0.00' dat from dual union all
select '2012-12-11' rq,'22:00:00' sj,'0.02' dat from dual
)select rn,rq,sj,dat
from
(
select rownum rn,bdate+rownum-1-(10/24) bdate,bdate+rownum-1+(10/24) edate
from
(
select to_date(min(rq),'yyyy-mm-dd') bdate,
to_date(max(rq),'yyyy-mm-dd') edate
from t1
)
connect by rownum <= edate - bdate + 2
) left join t1 on to_date(rq||' '||sj,'yyyy-mm-dd hh24:mi:ss') between bdate and edate rn rq sj dat
------------------------------------------------------------
1 1 2012-12-10 00:00:00 0.01
2 1 2012-12-10 08:00:00 0.00
3 2 2012-12-10 18:00:00 0.00
4 2 2012-12-11 02:00:00 0.04
5 2 2012-12-11 10:00:00 0.00
6 3 2012-12-11 14:00:00 0.00
7 3 2012-12-11 22:00:00 0.02
select to_date(to_char(rq, 'yyyy-MM-DD') || sj, 'yyyy-MM-DD hh24:mi:ss') + 10/24 rqsj,dat
From t_tprptdetail where ……
最终得到结果:
select trunc(rqsj) rq,sum(yl1) s from (
select to_date(to_char(rq, 'yyyy-MM-DD') || sj,
'yyyy-MM-DD hh24:mi:ss')+10/24 as rqsj,
yl1
from t_tprptdetail
where to_date(to_char(rq, 'yyyy-MM-DD') || sj,
'yyyy-MM-DD hh24:mi:ss') >=
(to_date('2012-12-11' || '14:00:00', 'yyyy-MM-DD hh24:mi:ss') - 1)
and to_date(to_char(rq, 'yyyy-MM-DD') || sj,
'yyyy-MM-DD hh24:mi:ss') <=
to_date('2012-12-13' || '10:00:00', 'yyyy-MM-DD hh24:mi:ss')
) group by trunc(rqsj) rq s
1 2012-12-11 6
2 2012-12-12
3 2012-12-13
已经很接近我想要的 谢谢你