大家好!
想请教一个问题,我有一个表
ID Productcode num createdate .......
1 PSR0W001 1 2007-01-02 09:30:01
2 PSR0W002 3 2007-01-02 01:30:12
3 PSR0W001 4 2007-01-02 10:10:10
4 PSR0W001 2 2007-01-03 03:00:15
5 PSR0W001 2 2007-01-03 08:01:00
..........
每天的08:00:00到第二天早上的07:59:59为一天(用以统计每天的产量)
如产品代码为PSR0W001的产品2007-01-02这天生产了7个。
请问怎么样用SQL语句实现啊?
想请教一个问题,我有一个表
ID Productcode num createdate .......
1 PSR0W001 1 2007-01-02 09:30:01
2 PSR0W002 3 2007-01-02 01:30:12
3 PSR0W001 4 2007-01-02 10:10:10
4 PSR0W001 2 2007-01-03 03:00:15
5 PSR0W001 2 2007-01-03 08:01:00
..........
每天的08:00:00到第二天早上的07:59:59为一天(用以统计每天的产量)
如产品代码为PSR0W001的产品2007-01-02这天生产了7个。
请问怎么样用SQL语句实现啊?
Select Productcode,sum(num) as nums,
to_char(Createdate,'yyyy-mm-dd') as Createdate
from (
Select Productcode,num,Createdate+(
case when to_char(Createdate,'hh24')<'08'
then -1 else 0 end) as Createdate from 表名 ) t
group by Productcode,to_char(Createdate,'yyyy-mm-dd')
Select Productcode,to_char(Createdate-1/3,'yyyy-mm-dd') as Createdate,sum(num) as nums
from table_name
group by Productcode,to_char(Createdate-1/3,'yyyy-mm-dd')
2 from (select tt.*,
3 decode(sign(to_char(tt.createdate,'HH24')-'08'),-1,to_char(tt.createdate-1,'yyyy-mm-dd'),to_char(tt.createdate,'yyyy-mm-dd')) as sumdate
4 from ( select 1 as id,'PSR0W001' as Productcode,1 as num,to_date('2007-01-02 09:30:01','yyyy-mm-dd hh24:mi:ss') as createdate from dual
5 union all
6 select 2 as id,'PSR0W002' as Productcode,3 as num,to_date('2007-01-02 13:30:12','yyyy-mm-dd hh24:mi:ss') as createdate from dual
7 union all
8 select 3 as id,'PSR0W001' as Productcode,4 as num,to_date('2007-01-02 23:10:10','yyyy-mm-dd hh24:mi:ss') as createdate from dual
9 union all
10 select 4 as id,'PSR0W001' as Productcode,2 as num,to_date('2007-01-03 03:00:15','yyyy-mm-dd hh24:mi:ss') as createdate from dual
11 union all
12 select 5 as id,'PSR0W001' as Productcode,2 as num,to_date('2007-01-03 08:01:00','yyyy-mm-dd hh24:mi:ss') as createdate from dual
13 )tt
14 )zz
15 group by zz.Productcode,zz.sumdate;PRODUCTCODE SUM_NUM
----------- ----------
PSR0W001 7
PSR0W001 2
PSR0W002 3
trunc(sysdate-1/3)和trunc(sysdate)-1/3有什么区别呢?
把当前时间-8小时后转换成整数(无小数位)
假设当前时间是2007-08-31 08:00:00
转换出来的时间就是2007-08-31
和trunc(sysdate)-1/3有什么区别呢?
把当前时间转换成整数(无小数位)后减8小时
假设当前时间是2007-08-31 08:00:00
转换出来的时间就是2007-08-30 16:00:00
谢谢大家了,特别是hebo(2005)解释的不错啊。