已知SQL语句:
SELECT Begdate,Enddate, sum(Unckqty) as Unckqty from (
SELECT * FROM ( SELECT TRUNC(((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)) Begdate ,
TRUNC( ((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)+3 ) Enddate,
COUNT (Vendor) Unckqty FROM TABLE1 WHERE (Cfm_status=0)
GROUP BY ((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24),
((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)+3)
WHERE Begdate>=24
)group by Begdate,Enddate ORDER BY Begdate
得到查询结果:
BEGDATE ENDDATE UNCKQTY
24 27 12
25 28 1
26 29 2
27 30 5
... ... ...
现在要得到如下结果:
BEGDATE ENDDATE UNCKQTY
24 27 12
28 31 1
32 35 2
36 39 5
... ... ...
请问如何实现
如果那位兄弟,解决了,觉的分少,说下,马上申请新号再给分~~~~
SELECT Begdate,Enddate, sum(Unckqty) as Unckqty from (
SELECT * FROM ( SELECT TRUNC(((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)) Begdate ,
TRUNC( ((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)+3 ) Enddate,
COUNT (Vendor) Unckqty FROM TABLE1 WHERE (Cfm_status=0)
GROUP BY ((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24),
((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*24)+3)
WHERE Begdate>=24
)group by Begdate,Enddate ORDER BY Begdate
得到查询结果:
BEGDATE ENDDATE UNCKQTY
24 27 12
25 28 1
26 29 2
27 30 5
... ... ...
现在要得到如下结果:
BEGDATE ENDDATE UNCKQTY
24 27 12
28 31 1
32 35 2
36 39 5
... ... ...
请问如何实现
如果那位兄弟,解决了,觉的分少,说下,马上申请新号再给分~~~~
解决方案 »
- oracle存储过程中,通过DBLINK执行远程插入SQL语句报错...
- ORACLE监听器不支持服务问题,急问,谢谢!
- java开发存储过程,如何让结果返回一个或多个记录集,请大家给点建议
- 数据库导出与导入.请教大侠
- 请问用imp 导入 带嵌套表的数据表具体应该做?
- oracle sqlplus 中编辑上一语句的命令是什么?
- 怎么把表a中的数据插入到表b中
- 这种问题大家没有吗?oracle与java
- 能否把一个数据库中的内容导入到另外一个数据库,两者的sid不同.
- 能否更新oracle 8i库中按照某个字段降序排列后的前几条记录呢?
- 请问在PROC中使用游标后,是先提交事务,还是先关闭游标?智者见智贴!无分了!
- 如何修改这条SQL语句才能实现所要求的效果~~急~~~~
SELECT Begdate + (mynum-1)*4, Enddate + (mynum-1)*4, sum(Unckqty) as Unckqty
from (SELECT *
FROM (SELECT TRUNC(((sysdate - (CASE
WHEN (Upd_date = '' or Upd_date is null) THEN
Pur_date
ELSE
Upd_date
END)) * 24)) Begdate,
TRUNC(((sysdate - (CASE
WHEN (Upd_date = '' or Upd_date is null) THEN
Pur_date
ELSE
Upd_date
END)) * 24) + 3) Enddate,
COUNT(Vendor) Unckqty,rownum mynum
FROM TABLE1
WHERE (Cfm_status = 0)
GROUP BY ((sysdate - (CASE
WHEN (Upd_date = '' or Upd_date is null) THEN
Pur_date
ELSE
Upd_date
END)) * 24),
((sysdate - (CASE
WHEN (Upd_date = '' or Upd_date is null) THEN
Pur_date
ELSE
Upd_date
END)) * 24) + 3)
WHERE Begdate >= 24)
group by Begdate, Enddate
ORDER BY Begdate
如果我没有理解错误的话,可以发现只需要按照开始时间做每四个小时的分组就可以了,分组条件加上Enddate没有什么意义,只需要加上3个小时表示一个时间就可以了。
你可以试试下面的语句:
SELECT Begdate,Enddate, sum(Unckqty) as Unckqty from (
SELECT TRUNC(((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*6))*4 Begdate ,
TRUNC( ((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*6)) *4 + 3 Enddate,
COUNT (Vendor) Unckqty FROM TABLE1 WHERE (Cfm_status=0)
GROUP BY TRUNC((sysdate-(CASE WHEN (Upd_date='' or Upd_date is null) THEN Pur_date ELSE Upd_date END) )*6) *4
) WHERE Begdate>=24
group by Begdate,Enddate ORDER BY Begdate ;