select to_char(datefield,'yyyy-mm-d'),count(*) from mytable group by to_char(datefield,'yyyy-mm-d')统计出来的结果是按星期分组,不是按旬
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,'中旬','下旬');
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,'中旬','下旬');
呵呵,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,'中旬','下旬')
to_char(datefield,'yyyy'), to_char(datefield,'mm'), decode(trunc(to_char(datefield,'dd')-1/10),0,'上旬',1,'中旬','下旬');
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,'中旬','下旬');
我的语句是有问题,把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,'中旬','下旬')