很急的问题 按周统计和按月统计的问题

Question

select count(*) from table where to_char(createtime,'ww')=to_char(sysdate,'ww')
今天所在的周的发贴数to_char(sysdate,'ww')可以换成你想查的周数.select count(*) from table where to_char(createtime,'mm')=to_char(sysdate,'mm')
今天所在的月的发贴数to_char(sysdate,'mm')可以换成你想查的月数.后面两个类似了,自己写吧

Answer 1

Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 
Connected as kdcerp20SQL> 
SQL> create table TEST0123
  2  (
  3    id int,
  4  title varchar(20),
  5  createtime  DATE,
  6  clickhits int
  7  )
  8  /Table createdSQL> insert into test0123 values(1,'t1',to_date('2006-02-01','yyyy-mm-dd'),100);1 row insertedSQL> insert into test0123 values(2,'t2',to_date('2006-02-12','yyyy-mm-dd'),200);1 row insertedSQL> insert into test0123 values(3,'t3',to_date('2006-02-14','yyyy-mm-dd'),210);1 row insertedSQL> insert into test0123 values(4,'t4',to_date('2006-01-14','yyyy-mm-dd'),230);1 row insertedSQL> commit;Commit completeSQL> select * from test0123;                                     ID TITLE                CREATETIME                                CLICKHITS
--------------------------------------- -------------------- ----------- ---------------------------------------
                                      1 t1                   2006-2-1                                        100
                                      2 t2                   2006-2-12                                       200
                                      3 t3                   2006-2-14                                       210
                                      4 t4                   2006-1-14                                       230SQL> SELECT to_char(createtime,'yyyymm') 年月, '第' || to_char(createtime,'w') || '周' 周, COUNT(ID) 发贴数  FROM test0123 GROUP BY to_char(createtime,'yyyymm'),to_char(createtime,'w');年月   周        发贴数
------ ----- ----------
200601 第2周          1
200602 第1周          1
200602 第2周          2SQL> SELECT to_char(createtime,'yyyymm') 年月,  COUNT(ID) 发贴数  FROM test0123 GROUP BY to_char(createtime,'yyyymm');年月       发贴数
------ ----------
200601          1
200602          3
SQL> SELECT a.title 文章,b.年月,b.周,b.clickhits最高 FROM
  2  test0123 a,
  3  (SELECT to_char(createtime,'yyyymm') 年月, '第' || to_char(createtime,'w') || '周' 周, MAX(clickhits) clickhits最高  FROM test0123 GROUP BY to_char(createtime,'yyyymm'),to_char(createtime,'w')) b WHERE a.clickhits=b.clickhits最高
  4  /文章                 年月   周    CLICKHITS最高
-------------------- ------ ----- -------------
t1                   200602 第1周           100
t3                   200602 第2周           210
t4                   200601 第2周           230SQL> 每月最高楼主自己写吧！