求连续登录的用户总数(有点难度，高手进)

Question

建表语句如下:
create table tb(dtime varchar2(30), accountname varchar2(10));insert into tb(dtime, accountname) values('20110513','A');
insert into tb(dtime, accountname) values('20110513','B');
insert into tb(dtime, accountname) values('20110513','C');
insert into tb(dtime, accountname) values('20110513','D');
insert into tb(dtime, accountname) values('20110514','A');
insert into tb(dtime, accountname) values('20110514','B');
insert into tb(dtime, accountname) values('20110514','C');
insert into tb(dtime, accountname) values('20110514','E');insert into tb(dtime, accountname) values('20110515','A');
insert into tb(dtime, accountname) values('20110515','B');
insert into tb(dtime, accountname) values('20110515','E');
insert into tb(dtime, accountname) values('20110515','F');insert into tb(dtime, accountname) values('20110516','A');
insert into tb(dtime, accountname) values('20110516','E');
insert into tb(dtime, accountname) values('20110516','I');
insert into tb(dtime, accountname) values('20110516','J');
commit;
dtime: 登录时间  accountname：登录用户根据输入的开始和结束时间，要求开始时间显示新增用户总数，其余时间显示新增用户连续登录的总数
例如：
场景1：
输入:20110513-20110514  
输出:20110513    20110514
     4           3
     
说明：20110513新增了4个用户ABCD，故为4；  20110514登录了ABC，故为3；场景2：
输入:20110513-20110515  
输出:20110513    20110514     20110515
     4           3            2
     
说明：20110513新增了4个用户ABCD，故为4；  20110514登录了ABC，故为3；20110515登录了AB，故为2；场景3：
输入:20110513-20110516  
输出:20110513    20110514     20110515     20110516
     4           3            2            1
     
说明：20110513新增了4个用户ABCD，故为4；  20110514登录了ABC，故为3；20110515登录了AB，故为2；20110516登录了A，故为1
场景4：
输入:20110514-20110515  
输出:20110513    20110514
     1           1
说明：20110514新增了用户E，故为1；20110515 登录了E，故为1

Answer 1

格式化下。
要求：根据输入的开始和结束时间，要求开始时间显示新增用户总数，其余时间显示新增用户连续登录的总数。
例如：
场景1：
输入:20110513-20110514  
输出:20110513    20110514
     4           3
     
说明：20110513新增了4个用户ABCD，故为4；  20110514登录了ABC，故为3；场景2：
输入:20110513-20110515  
输出:20110513    20110514     20110515
     4           3            2
     
说明：20110513新增了4个用户ABCD，故为4；  20110514登录了ABC，故为3；20110515登录了AB，故为2；场景3：
输入:20110513-20110516  
输出:20110513    20110514     20110515     20110516
     4           3            2            1
     
说明：20110513新增了4个用户ABCD，故为4；  20110514登录了ABC，故为3；20110515登录了AB，故为2；20110516登录了A，故为1
场景4：
输入:20110514-20110515  
输出:20110513    20110514
     1           1
说明：20110514新增了用户E，故为1；20110515 登录了E，故为1

Answer 2

--尝试了下，用最基本本的来做的，
--过程如下：
CREATE OR REPLACE procedure p_accoutname(stime varchar2,etime varchar2,cur  out sys_refcursor )
is
sql_create varchar2(3000):='create table tmp( ';
sql_insert varchar2(3000):='insert into tmp values(';
cnt number;
cnt2 number:=1;
begin
     select count(1) into cnt from user_tables where table_name=upper('tmp') ;
     if cnt !=0 then
        execute immediate 'drop table tmp';
     end if;
     for i in stime..etime loop         sql_create:=sql_create||'"'||i||'" varchar2(30),';
         if cnt2=1 then
            select count(1) into cnt from tb a where dtime=i and
                not exists(select 1 from tb b where a.accountname=b.accountname and b.dtime<a.dtime) ;
            cnt2:=0;
         else
             select count(1) into cnt  from tb c ,
                             (select a.*  from tb a where dtime=stime and not exists
                             (select 1 from tb b where a.accountname=b.accountname and b.dtime<a.dtime)
                            ) d
             where c.accountname=d.accountname and c.dtime=i ;
         end if;
         sql_insert:=sql_insert||cnt||',';
     end loop;
     sql_create:=rtrim(sql_create,',') ;
     sql_insert:=rtrim(sql_insert,',') ;
     sql_create:=sql_create||')';
     sql_insert:=sql_insert||')';
     --dbms_output.put_line(sql_create);
     --dbms_output.put_line(sql_insert);
     execute immediate sql_create;
     execute immediate sql_insert;
     open cur for 'select * from tmp' ;end;
/
--测试:SQL*Plus: Release 8.0.6.0.0 - Production on 星期二 5月 17 16:25:32 2011(c) Copyright 1999 Oracle Corporation.  All rights reserved.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> set serveroutput on
SQL> set linesize 32767
SQL> col "20110513" format a10
SQL> col "20110514" format a10
SQL> col "20110515" format a10
SQL> col "20110516" format a10
SQL> var cur refcursor
SQL> exec p_accoutname('20110513','20110514',:cur);PL/SQL procedure successfully completed.SQL> print cur20110513   20110514
---------- ----------
4          3SQL> exec p_accoutname('20110513','20110515',:cur);PL/SQL procedure successfully completed.SQL> print cur20110513   20110514   20110515
---------- ---------- ----------
4          3          2SQL> exec p_accoutname('20110513','20110516',:cur);PL/SQL procedure successfully completed.SQL> print cur20110513   20110514   20110515   20110516
---------- ---------- ---------- ----------
4          3          2          1SQL> exec p_accoutname('20110514','20110515',:cur);PL/SQL procedure successfully completed.SQL> print cur20110514   20110515
---------- ----------
1          1SQL> exec p_accoutname('20110514','20110516',:cur);PL/SQL procedure successfully completed.SQL> print cur20110514   20110515   20110516
---------- ---------- ----------
1          1          1SQL> 

Answer 3

楼上的似乎有点复杂了，试试一个SQL

Answer 4

--确实杨哥说的是，当初是因为我有个地方卡住了，所以换成procedure了，现在解决了：with tab as(
select '20110514' stime,'20110517' etime from dual
)
select dt,
       case when lv =1 then 
            (select count(1)  from tb a where dtime=dt and
                not exists(select 1 from tb b where a.accountname=b.accountname and b.dtime<a.dtime)
             )
       else --就是这里不知道怎么把最外层的stime值传进到最内侧，所以才想用procedure来做。
            (select count(1) from tb c ,
                 (select a.*  from tb a,tab where dtime=stime --这里参数stime在最外侧，因此不能传到这里来，这个问题解决了
                  and not exists (select 1 from tb b where a.accountname=b.accountname and b.dtime<a.dtime)
                 ) d
             where c.accountname=d.accountname and c.dtime=dt
            )
       end as cnt
from (
select stime,stime+level-1 dt,level lv
from tab 
connect by level<=etime-stime+1
)

Answer 5

学习学习!
确实复杂，看sql就头晕！
又是内联视图，又是case分支，又是connect by的.........

Answer 6

insert into tb(dtime, accountname) values('20110514','E');还有这个。。怎么是3呢应该也是4吧。。