怎样求系统日期前一天的数据按旬统计是这样,比如系统日期为10号,就求1-10号num字段的和;如果系统日期为15就求11-15号num字段的和;如果是28号,就求21-28号num字段的和
解决方案 »
- oracle Redo Buffers调整
- 高手请进--住址查出编码code
- 各位父老乡亲们,多进来给我出出主意
- Oracle里能保存多少Session?急,高手进
- 急求啊!oralce数据被误删了怎么恢复吗?没有数据备份!能恢复之前的操作吗??
- SQL语句优化—使用DECODE函数时可否不遍历整个数据表?
- 高分请教,如何在PB6.5中存取ORACLE的BLOB字段?急急急...
- 一次提交执行多条SQL语句(用;分隔开)类似执行批语句的问题,急求答案!!!!
- 急救oracle Imp 与Exp 问题???
- 大侠们,请问我如何将序列号减一。。。
- 用FORM BUILDER 去打开一个FMB 文件,结果出了FRM-10043:打不开文件
- 左右连接right join 和 (+)= 区别
sysdate-1
求旬的
decode(trunc(to_number(to_char(sysdate,'dd'))/10),0,1,1,2,3)
这样出来的1-9是1,10-19是2,20-31是3
然后分组统计按这个字段就行了
先来个清楚明了的,假设有个函数用于判断是否同旬的
create or replace function IsSameXun(P_date in date,P_date2 in date) return int
is
begin
if ceil(to_number(to_char(p_Date,'dd'))/10)=ceil(to_number(to_char(p_Date2,'dd'))/10) then
return 1;
else
return 0;
end if;
end;select sum(case when IsSameXun(sysdate,theday)=1 then num else 0 end) from xxx where ...
theday 是表格中表示日期的另外一个字段(假设而已,肯定应该还有另外一日期字段)
一般oracle做外联接有简化写法,看起来比较清楚
比如A B两个表都是ID关联,a left join B
可以用
select 字段1,字段2
from a,b
where a.id=b.id(+)
group by 字段1,字段2
111 2008-05-01 1
121 2008-05-06 1
111 2008-05-07 1
222 2008-05-09 1按照产品ID分组,分别将本日\本旬\本月的销售量统计出来
SUM (DECODE (TRUNC ((TO_NUMBER (TO_CHAR (SYSDATE, 'dd')) - 1) / 10),
0, CASE
WHEN TO_CHAR (rq, 'dd') >= '1'
AND TO_CHAR (rq, 'dd') <= TO_CHAR (SYSDATE, 'dd')
THEN salenum
ELSE 0
END,
1, CASE
WHEN TO_CHAR (rq, 'dd') >= '11'
AND TO_CHAR (rq, 'dd') <= TO_CHAR (SYSDATE, 'dd')
THEN salenum
ELSE 0
END,
CASE
WHEN TO_CHAR (rq, 'dd') >= '21'
AND TO_CHAR (rq, 'dd') <= TO_CHAR (SYSDATE, 'dd')
THEN salenum
ELSE 0
END
)
) cur_ten_days,
SUM (DECODE (TO_CHAR (rq, 'yyyymmdd'),
TO_CHAR (SYSDATE, 'yyyymmdd'), salenum,
0
)
) cur_day
FROM a
WHERE TO_CHAR (rq, 'yyyymm') = TO_CHAR (SYSDATE, 'yyyymm')
GROUP BY productid
修改过,前面是插入测试数据WITH a AS
(SELECT '111' productid, TO_DATE ('2008-05-01', 'yyyy-mm-dd') rq,
1 salenum
FROM DUAL
UNION ALL
SELECT '121', TO_DATE ('2008-05-06', 'yyyy-mm-dd'), 1
FROM DUAL
UNION ALL
SELECT '111', TO_DATE ('2008-05-07', 'yyyy-mm-dd'), 1
FROM DUAL
UNION ALL
SELECT '222', TO_DATE ('2008-05-09', 'yyyy-mm-dd'), 1
FROM DUAL)SELECT productid, SUM (salenum) cur_mon,
SUM (DECODE (TRUNC ((TO_NUMBER (TO_CHAR (SYSDATE, 'dd')) - 1) / 10),
0, CASE
WHEN TO_CHAR (rq, 'dd') >= '01'
AND TO_CHAR (rq, 'dd') <= TO_CHAR (SYSDATE, 'dd')
THEN salenum
ELSE 0
END,
1, CASE
WHEN TO_CHAR (rq, 'dd') >= '11'
AND TO_CHAR (rq, 'dd') <= TO_CHAR (SYSDATE, 'dd')
THEN salenum
ELSE 0
END,
CASE
WHEN TO_CHAR (rq, 'dd') >= '21'
AND TO_CHAR (rq, 'dd') <= TO_CHAR (SYSDATE, 'dd')
THEN salenum
ELSE 0
END
)
) cur_ten_days,
SUM (DECODE (TO_CHAR (rq, 'yyyymmdd'),
TO_CHAR (SYSDATE, 'yyyymmdd'), salenum,
0
)
) cur_day
FROM a
WHERE TO_CHAR (rq, 'yyyymm') = TO_CHAR (SYSDATE, 'yyyymm')
GROUP BY productid结果
Row# PRODUCTID CUR_MON CUR_TEN_DAYS CUR_DAY1 121 1 1 0
2 111 2 2 0
3 222 1 1 1
WITH a AS
(SELECT '111' productid, TO_DATE ('2008-05-01', 'yyyy-mm-dd') rq,
1 salenum
FROM DUAL
UNION ALL
SELECT '121', TO_DATE ('2008-05-06', 'yyyy-mm-dd'), 1
FROM DUAL
UNION ALL
SELECT '111', TO_DATE ('2008-05-07', 'yyyy-mm-dd'), 1
FROM DUAL
UNION ALL
SELECT '222', TO_DATE ('2008-05-09', 'yyyy-mm-dd'), 1
FROM DUAL
UNION ALL
SELECT '222', TO_DATE ('2008-05-19', 'yyyy-mm-dd'), 1
FROM DUAL)
productid rq salenum
111 2008-05-01 1
121 2008-05-06 1
111 2008-05-07 1
222 2008-05-09 1
222 2008-05-19 1 出来的结果
Row# PRODUCTID CUR_MON CUR_TEN_DAYS CUR_DAY1 121 1 1 0
2 111 2 2 0
3 222 2 1 1