ORACLE里面按旬统计怎么GROUP BY?

[sxlcj]

select to_char(datefield,'yyyy-mm-dd'),count(*) from mytable group by
to_char(datefield,'yyyy-mm-dd'),是按天统计,按旬怎么写?

[jiezhi]

按旬?
不如再添加一個字段，標識該日期是當月的上旬、中旬還是下旬。
然后以該字段進行分組。

[tsj68]

select to_char(datefield,'yyyy-mm-d'),count(*) from mytable group by to_char(datefield,'yyyy-mm-d')

[sxlcj]

我测试一下tsj68说的.

[sxlcj]

select to_char(datefield,'yyyy-mm-d'),count(*) from mytable group by to_char(datefield,'yyyy-mm-d')统计出来的结果是按星期分组,不是按旬

[bzszp]

select to_char(datefield,'yyyy-mm-dd'),count(*) from mytable group by
to_char(datefield,'yyyy'), to_char(datefield,'mm'), decode(trunc(to_char(datefield,'dd')-1/10),0,'上旬'，1,'中旬','下旬');

[bobfang]

select to_char(datefield,'yyyy'),
to_char(datefield,'mm'),
decode(trunc((to_char(datefield,'dd')-1)/10),0,'上旬'，1,'中旬','下旬'),
count(*)
from mytable
group by to_char(datefield,'yyyy'),
to_char(datefield,'mm'),
decode(trunc((to_char(datefield,'dd')-1)/10),0,'上旬'，1,'中旬','下旬');

[sxlcj]

多谢bobfang

[tsj68]

呵呵,bobfang的语句挺不错的.
我的语句是有问题,把1-9日算到第0旬,10-19算到第1旬,20-29算到第2旬,30-31日算到第3旬了.
可以这样改一下:

select decode(to_char(datefield-1,'yyyy-mm-d'),0,'上旬',1,'中旬','下旬'),count(*) from mytable group by decode(to_char(datefield-1,'yyyy-mm-d'),0,'上旬',1,'中旬','下旬')

[zzxxdd]

up

[ahalf]

使用维试试