原始数据表格,表名ta(名称,name|日期SDate|数量SumNum)名称 | 日期 | 数量
----------------------------------------
A 2006-01-01 10
A 2006-01-02 10
A 2006-01-03 10
A 2006-01-04 10
A 2006-01-05 10
A 2006-01-06 10
A 2006-01-07 10
A 2006-01-08 10
A 2006-01-09 10
A 2006-01-10 10
A 2006-01-11 10
A 2006-01-12 10
A 2006-01-13 10
A 2006-01-14 10
A 2006-01-15 10
A 2006-01-16 10
B 2006-01-01 10
B 2006-01-02 10
B 2006-01-03 10
B 2006-01-04 10
B 2006-01-05 10
B 2006-01-06 10
B 2006-01-07 10
B 2006-01-08 10
B 2006-01-09 10
B 2006-01-10 10
B 2006-01-11 10
B 2006-01-12 10
B 2006-01-13 10
B 2006-01-14 10
B 2006-01-15 10
B 2006-01-16 10现在要按名称和时间周期进行汇总,时间周期是4天,最小汇总时间指定是2006-01-01,汇总的结果如下,在oracle里怎样实现名称 | 日期 | 数量
----------------------------------------
A 2006-01-04 40
A 2006-01-08 40
A 2006-01-12 40
A 2006-01-16 40
B 2006-01-04 40
B 2006-01-08 40
B 2006-01-12 40
B 2006-01-16 40
请问这样的sql语句在oracle怎样写
在mssql可以这样写
select dateadd(dd,(datediff(dd,'2005-1-1',tDate)/4)*4,'2005-1-1'),sum(tnum) from testgroup by dateadd(dd,(datediff(dd,'2005-1-1',tDate)/4)*4,'2005-1-1')
----------------------------------------
A 2006-01-01 10
A 2006-01-02 10
A 2006-01-03 10
A 2006-01-04 10
A 2006-01-05 10
A 2006-01-06 10
A 2006-01-07 10
A 2006-01-08 10
A 2006-01-09 10
A 2006-01-10 10
A 2006-01-11 10
A 2006-01-12 10
A 2006-01-13 10
A 2006-01-14 10
A 2006-01-15 10
A 2006-01-16 10
B 2006-01-01 10
B 2006-01-02 10
B 2006-01-03 10
B 2006-01-04 10
B 2006-01-05 10
B 2006-01-06 10
B 2006-01-07 10
B 2006-01-08 10
B 2006-01-09 10
B 2006-01-10 10
B 2006-01-11 10
B 2006-01-12 10
B 2006-01-13 10
B 2006-01-14 10
B 2006-01-15 10
B 2006-01-16 10现在要按名称和时间周期进行汇总,时间周期是4天,最小汇总时间指定是2006-01-01,汇总的结果如下,在oracle里怎样实现名称 | 日期 | 数量
----------------------------------------
A 2006-01-04 40
A 2006-01-08 40
A 2006-01-12 40
A 2006-01-16 40
B 2006-01-04 40
B 2006-01-08 40
B 2006-01-12 40
B 2006-01-16 40
请问这样的sql语句在oracle怎样写
在mssql可以这样写
select dateadd(dd,(datediff(dd,'2005-1-1',tDate)/4)*4,'2005-1-1'),sum(tnum) from testgroup by dateadd(dd,(datediff(dd,'2005-1-1',tDate)/4)*4,'2005-1-1')
解决方案 »
- AIX系统中oracle在登录时报ORA-01034:ORACLE不可用
- 两个比较简单的oracle问题
- 如何查询字段中不包含字母的值
- if 条件1 成立 then 执行 select 1 from a elseif if 条件2 成立 select 1 from b
- 怎么将oracle数据库的al16utf16字符集修改成zhs16gbk ??
- 有没办法快速的把记住公司数据库各个表的功能与相关存储功能等
- 新手问一个关于SQL PLUS信息输出格式的简单问题,大家请进来看看吧。
- 实体化视图刷新不正常(在线等,快来救命啊)
- 回滚段太小,怎么解决啊
- 一个表的数据用另一个表的数据更新(基本应用)不行?
- ORA-01489: result of string concatenation is too long
- 又是一个关于输出格式的问题
from test
group by tName,to_date('2006-01-01','yyyy-mm-dd')+(trunc(tDate-to_date('2006-01-01','yyyy-mm-dd'))/4)*4
2 SUM(SumNum) FROM TA
3 GROUP BY NAME, TRUNC(TO_NUMBER(SDATE - TO_DATE('2006-01-01','YYYY-MM-DD'))/4)*4
4 /NAME DAY SUM(SUMNUM)
---- ----------- -----------
A 2006-1-4 40
A 2006-1-8 40
A 2006-1-12 40
A 2006-1-16 40
B 2006-1-4 40
B 2006-1-8 40
B 2006-1-12 40
B 2006-1-16 408 rows selected