按周统计实例表userlog:
结构如下:
SQL> desc userlog;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
LOGDATE DATE Y sysdate
USERNAME VARCHAR2(20) Y
LOGSTATS VARCHAR2(20) Y
数据如下:
SQL> select * from userlog where rownum<5;
LOGDATE USERNAME LOGSTATS
----------- -------------------- --------------------
2006-07-03 FIREFLY OFF
2006-07-03 FIREFLY OFF
2006-07-03 FIREFLY OFF
2006-07-03 FIREFLY ON
这个不知道是不是您要的结果:
SQL> SELECT to_char(trunc(MIN(b.logdate)),'YYYY-MM-DD')||'~'||to_char(trunc(MAX(b.logdate)),'YYYY-MM-DD') AS weekdate,
2 c.total
3 FROM userlog b,
4 (
5 SELECT to_char(a.logdate,'WW') AS weekday,COUNT(*) AS total
6 FROM userlog a
7 GROUP BY to_char(a.logdate,'WW')
8 ) c
9 WHERE to_char(b.logdate,'WW')=c.weekday
10 GROUP BY c.total
11 ORDER BY to_char(trunc(MIN(b.logdate)),'YYYY-MM-DD')||'~'||to_char(trunc(MAX(b.logdate)),'YYYY-MM-DD')
12 /
WEEKDATE TOTAL
--------------------- ----------
2006-07-03~2006-07-07 392
2006-07-10~2006-07-11 122
也可以严格按周一到周日日期格式来统计:
SQL> SELECT monday||'~'||sunday AS weekday,SUM(1) FROM (
2 SELECT CASE
3 WHEN to_char(t.logdate,'D')=1 THEN
4 to_char(trunc(t.logdate-7),'YYYY-MM-DD')
5 ELSE
6 to_char(trunc(next_day(t.logdate,2)-7),'YYYY-MM-DD')
7 END AS monday,
8 CASE
9 WHEN to_char(t.logdate,'D')=1 THEN
10 to_char(trunc(t.logdate),'YYYY-MM-DD')
11 ELSE
12 to_char(trunc(next_day(t.logdate,1)),'YYYY-MM-DD')
13 END AS sunday,1
14 FROM Userlog t )
15 GROUP BY monday||'~'||sunday
16 /
WEEKDAY SUM(1)
--------------------- ----------
2006-07-03~2006-07-09 392
2006-07-10~2006-07-16 122