一条SQL解决操作统计查询

网站的操作统计可以用来查看用户的使用习惯、爱好等...

下面的表table是记录用户使用那些模块的内容

字段有业务代码（YWDM），用户的编号（RID）,访问时间(TIME)

我们在对网站的用户进行操作统计的时候，要得到那些用户在特定的时间使用了那些功能，可以通过这样一条语句得到具体列表

 

 

SELECT * FROM   
     (SELECT A.*, ROWNUM RN FROM   /*  增加rownum 用于分页  */
	           (select z.rid, (select count(*) from      /*  内部查询开始  */
                             wpw_query_zgcx 
                                   where rid = z.rid and ywdm='0619') as wcl, /* 将内部表与外部表连接z.rid=rid*/
                   (select count(*) from wpw_query_zgcx 
                                   where rid = z.rid and ywdm='0618') as zcl,
                   (select count(*) from wpw_query_zgcx 
                                   where rid = z.rid and ywdm='0805') as yjc,
                   (select count(*) from wpw_query_zgcx 
                                  where rid = z.rid and ywdm='0302') as fxczf,
                   (select count(*) from wpw_query_zgcx 
                                  where rid = z.rid and ywdm='2103') as qwsg,
                   (select count(*) from wpw_query_zgcx 
                                  where rid = z.rid and ywdm='1601') as qzcs,
                   (select count(*) from wpw_query_zgcx 
                                  where rid = z.rid and ywdm='2201') as sjsb,
                   (select count(*) from wpw_query_zgcx 
                                  where rid = z.rid and ywdm='0501') as ptxx,
                   (select count(*) from wpw_query_zgcx 
                                  where rid = z.rid and ywdm='1701') as ptdd 
          from wpw_query_zgcx z       /*    */
                       where time > to_date('2010-06-01 00:00', 'yyyy-mm-dd hh24:mi:ss') 
                         and time < to_date('2011-06-02 18:21', 'yyyy-mm-dd hh24:mi:ss') 
          group by z.rid order by z.rid desc) A )   /* 根据用户编号分组、排序  */
where RN between '1' and '10   /* 根据开始页和结束页分组  */

 如果要将内部开始查询地方的rid转换成用户名称可以替换成一条语句：

select user_name from users where user_id=z.rid