表A
字段 A B
2007-09-27 2:00 -3
2007-09-27 4:00 200
2007-09-27 4:00 -100
2007-09-27 17:00 -4
如何写这个SQL?得到的结果在页面上显示为:A按小时排序,B、C空值补0,如果B、C有值,则B显示正数,C显示负数。
A B C
2007-09-27 1:00 0 0
2007-09-27 2:00 0 -3
2007-09-27 3:00 0 0
2007-09-27 4:00 200 -100
........
2007-09-27 24:00 0 0
字段 A B
2007-09-27 2:00 -3
2007-09-27 4:00 200
2007-09-27 4:00 -100
2007-09-27 17:00 -4
如何写这个SQL?得到的结果在页面上显示为:A按小时排序,B、C空值补0,如果B、C有值,则B显示正数,C显示负数。
A B C
2007-09-27 1:00 0 0
2007-09-27 2:00 0 -3
2007-09-27 3:00 0 0
2007-09-27 4:00 200 -100
........
2007-09-27 24:00 0 0
SELECT DISTINCT T.A,
DECODE(A.B > 0, TRUE, A.B, 0) B,
DECODE(A.B < 0, TRUE, A.B, 0) C
FROM T, A
WHERE T.A = A.A;
没调试,lz全当参考
另外,这个前提是正点的时候最多2条数据,
比如如果4:00的时候有3条记录的话就没戏了
SELECT b.dd a, DECODE (aa.b, NULL, 0, aa.b) b, DECODE (aa.c,
NULL, 0,
aa.c
) c
FROM (SELECT TO_CHAR (TRUNC (a, 'hh24'), 'yyyy-mm-dd hh24:mi:ss') a,
SUM (DECODE (SIGN (b), -1, 0, b)) b,
SUM (DECODE (SIGN (b), -1, b, 0)) c
FROM a
GROUP BY TRUNC (a, 'hh24')) aa,
(SELECT TO_CHAR ( TRUNC (TO_DATE ('2007-09-27', 'yyyy-mm-dd'))
+ (LEVEL - 1) / 24,
'yyyy-mm-dd hh24:mi:ss'
) dd
FROM DUAL
CONNECT BY LEVEL < 25) b
WHERE b.dd = aa.a(+)
order by b.dd
这是结果:
A B C
------------------- ---------- ----------
2007-09-27 00:00:00 0 0
2007-09-27 01:00:00 0 0
2007-09-27 02:00:00 0 -3
2007-09-27 03:00:00 0 0
2007-09-27 04:00:00 200 -100
2007-09-27 05:00:00 0 0
2007-09-27 06:00:00 0 0
2007-09-27 07:00:00 0 0
2007-09-27 08:00:00 0 0
2007-09-27 09:00:00 0 0
2007-09-27 10:00:00 0 0A B C
------------------- ---------- ----------
2007-09-27 11:00:00 0 0
2007-09-27 12:00:00 0 0
2007-09-27 13:00:00 0 0
2007-09-27 14:00:00 0 0
2007-09-27 15:00:00 0 0
2007-09-27 16:00:00 0 0
2007-09-27 17:00:00 4 0
2007-09-27 18:00:00 0 0
2007-09-27 19:00:00 0 0
2007-09-27 20:00:00 0 0
2007-09-27 21:00:00 0 0A B C
------------------- ---------- ----------
2007-09-27 22:00:00 0 0
2007-09-27 23:00:00 0 0已选择24行。SQL> ORACLE不认24:00:00只有00:00:00。
select distinct (to_date(to_char(tt.A,'yyyy-mm-dd'),'yyyy-mm-dd HH24:mi:ss') + zz.rn/24) as mydate,
sum(decode(tt.A,to_date(to_char(tt.A,'yyyy-mm-dd'),'yyyy-mm-dd HH24:mi:ss') + zz.rn/24,decode(sign(tt.B),1,tt.B,0),0)) as ldata,
sum(decode(tt.A,to_date(to_char(tt.A,'yyyy-mm-dd'),'yyyy-mm-dd HH24:mi:ss') + zz.rn/24,decode(sign(tt.B),-1,tt.B,0),0)) as rdata
from (
select to_date('2007-09-27 2:00','yyyy-mm-dd HH24:mi') as A,-3 as B from dual
union all
select to_date('2007-09-27 4:00','yyyy-mm-dd HH24:mi') as A,200 as B from dual
union all
select to_date('2007-09-27 4:00','yyyy-mm-dd HH24:mi') as A,-100 as B from dual
union all
select to_date('2007-09-27 17:00','yyyy-mm-dd HH24:mi') as A,-4 as B from dual
)tt,
(
select rownum rn from all_objects where rownum <= 24
)zz
group by (to_date(to_char(tt.A,'yyyy-mm-dd'),'yyyy-mm-dd HH24:mi:ss') + zz.rn/24);
========================result==============================MYDATE LDATA RDATA
-------------------------------------------------
9/27/2007 1:00 0 0
9/27/2007 2:00 0 -3
9/27/2007 3:00 0 0
9/27/2007 4:00 200 -100
9/27/2007 5:00 0 0
9/27/2007 6:00 0 0
9/27/2007 7:00 0 0
9/27/2007 8:00 0 0
9/27/2007 9:00 0 0
9/27/2007 10:00 0 0
9/27/2007 11:00 0 0
9/27/2007 12:00 0 0
9/27/2007 13:00 0 0
9/27/2007 14:00 0 0
9/27/2007 15:00 0 0
9/27/2007 16:00 0 0
9/27/2007 17:00 0 -4
9/27/2007 18:00 0 0
9/27/2007 19:00 0 0
9/27/2007 20:00 0 0
9/27/2007 21:00 0 0
9/27/2007 22:00 0 0
9/27/2007 23:00 0 0
9/28/2007 0 0