countInfo表,记录来访信息,有id,viewTime等字段,现在要根据日期来取出每天访问总量,同时把所有当月的总访问量也一起取出,写的查询语句如下:select b.viewDay,b.viewDayCount,sum(b.viewDayCount) viewCount from countInfo a, (select count(id) viewDayCount,to_char(viewTime,'YYYY-MM-DD') viewDay from countInfo where to_char(viewTime,'YYYY-MM') = '2007-11' group by to_char(viewTime,'YYYY-MM-DD')) b group by to_char(a.viewTime,'YYYY-MM-DD'),b.viewDayCount order by b.viewDay 现在查询出来的sum(b.viewDayCount)这个,理论上来说应该每条记录都一样,是当月每天记录的汇总,可现在出现的情况是sum(b.viewDayCount)的值是当月总记录乘以当天总记录…………也就是
select count(id) viewDayCount,to_char(viewTime,'YYYY-MM-DD') viewDay from countInfo where to_char(viewTime,'YYYY-MM') = '2007-11' group by to_char(viewTime,'YYYY-MM-DD')
查询出来的值如下:viewDay viewDayCount
2007-11-02 1
2007-11-03 2
2007-11-24 1
2007-11-25 4而整条语句查询出来的是viewDay viewDayCount viewCount(这个字段应该都为8的啊…………)
2007-11-02 1 8
2007-11-03 2 16
2007-11-24 1 8
2007-11-25 4 32感觉应该是group by 的问题,可是因为对oracle实在不熟,实在想不出该怎么写。
另外,原想不需要countInfo a这个表,可是查出来的sum值和viewDayCount的值完全相同…………
select count(id) viewDayCount,to_char(viewTime,'YYYY-MM-DD') viewDay from countInfo where to_char(viewTime,'YYYY-MM') = '2007-11' group by to_char(viewTime,'YYYY-MM-DD')
查询出来的值如下:viewDay viewDayCount
2007-11-02 1
2007-11-03 2
2007-11-24 1
2007-11-25 4而整条语句查询出来的是viewDay viewDayCount viewCount(这个字段应该都为8的啊…………)
2007-11-02 1 8
2007-11-03 2 16
2007-11-24 1 8
2007-11-25 4 32感觉应该是group by 的问题,可是因为对oracle实在不熟,实在想不出该怎么写。
另外,原想不需要countInfo a这个表,可是查出来的sum值和viewDayCount的值完全相同…………
select b.viewDay,b.viewDayCount,sum(b.viewDayCount) viewCount from countInfo a, (select count(id) viewDayCount,to_char(viewTime,'YYYY-MM-DD') viewDay from countInfo where to_char(viewTime,'YYYY-MM') = '2007-11' group by to_char(viewTime,'YYYY-MM-DD')) b group by b.viewDay,b.viewDayCount order by b.viewDay
问题如上
select b.viewDay,
to_char(b.viewDay,'yyyymm')
b.viewDayCount,
sum(b.viewDayCount) over(partition by to_char(b.viewDay,'yyyymm') order by b.viewDay) viewCount
from (select count(id) viewDayCount,
to_char(viewTime, 'YYYY-MM-DD') viewDay
from countInfo
where to_char(viewTime, 'YYYY-MM') = '2007-11'
group by to_char(viewTime, 'YYYY-MM-DD')
)b;
viewTime是记录的来访时间,count(id)是为了根据来访的日期来汇总每一天的总访问量,至于是id还是别的无所谓吧?汗。
select count(id) viewDayCount,to_char(viewTime,'YYYY-MM-DD') viewDay from countInfo where to_char(viewTime,'YYYY-MM') = '2007-11' group by to_char(viewTime,'YYYY-MM-DD')
这句就是根据日期(to_char(viewTime,'YYYY-MM-DD'))来记录汇总的每天的访问量
然后用sum(b.viewDayCount)来记录当月所有的访问量。
我也不知道说得清不清楚,汗。总之countInfo是记录了每一次的访问,每天都会有很多条记录,这个会在日访问明细里面记录,而现在要显示的是月访问记录显示的是每天的总访问量及相对于月访问量的百分比……
明白了,用我前面写的SQL查询语句就可以出来了,试试看可以不...
to_char(b.viewDay,'yyyymm')
b.viewDayCount,试了,提示“未找到预期的from关键字”……汗。
select b.viewDay,
to_char(b.viewDay,'yyyymm'),
b.viewDayCount,
sum(b.viewDayCount) over(partition by to_char(b.viewDay,'yyyymm') order by b.viewDay) viewCount
from (select count(id) viewDayCount,
to_char(viewTime, 'YYYY-MM-DD') viewDay
from countInfo
where to_char(viewTime, 'YYYY-MM') = '2007-11'
group by to_char(viewTime, 'YYYY-MM-DD')
)b;
---------- -----------
1 2007-11-2
2 2007-11-3
3 2007-11-3
4 2007-11-24
5 2007-11-25
6 2007-11-256 rows selectedSQL>
SQL> select b.viewDay,
2 b.viewDayCount,
3 substr(b.viewDay,1,6),
4 sum(b.viewDayCount) over(partition by substr(b.viewDay,1,6)) viewCount
5 from (select count(1) viewDayCount,
6 to_char(viewTime, 'YYYYMMDD') viewDay
7 from countInfo
8 where to_char(viewTime, 'YYYYMM') = '200711'
9 group by to_char(viewTime, 'YYYYMMDD')
10 )b
11 order by 1;VIEWDAY VIEWDAYCOUNT SUBSTR(B.VIEWDAY,1,6) VIEWCOUNT
-------- ------------ --------------------- ----------
20071102 1 200711 6
20071103 2 200711 6
20071124 1 200711 6
20071125 2 200711 6
create table COUNTINFO
(
ID VARCHAR2(10) not null,
VIEWTIME DATE,(访问时间)
VIEWIP VARCHAR2(15),(IP地址)
VIEWBROWER VARCHAR2(50),(浏览器)
VIEWSYS VARCHAR2(50),(操作系统)
OUTTIME DATE(离开时间)
)这几个字段,目前只用到id和viewtime字段。就这个表,要根据访问时间算出当月每天的访问量和当月总访问最………………数据……如下id viewTime VIEWIP VIEWBROWER VIEWSYS OUTTIME54 2007-11-25 14:34:09 127.0.0.1 IE 7 Microsoft Windows 2003
2 2007-11-2 13:44:32 127.0.0.1 IE 7.X Microsoft Windows 2003
3 2007-11-3 13:44:32
4 2007-11-3 13:44:32
1 2007-11-24 13:44:32 127.0.0.1 IE 7 Microsoft Windows 2003
48 2007-11-25 13:34:02 127.0.0.1 IE 7.X Microsoft Windows 2003
50 2007-11-25 13:43:40 127.0.0.1 IE 7.X Microsoft Windows 2003
52 2007-11-25 13:44:32 127.0.0.1 IE 7 Microsoft Windows 2003
努力学习oracle中………………谢谢谢谢