有一个表 a 它有三个字段
empid workdate calot07-11817 2008-8-6 2.0
07-11817 2008-8-7 1.5
07-11817 2008-8-8 2.5
07-11817 2008-8-15 2.0
07-11817 2008-8-19 1.5
07-11818 2008-8-20 0.5
07-11818 2008-8-21 3.0
07-11819 2008-8-26 2.5
07-11819 2008-8-28 2.0
07-11819 2008-8-29 0.5 我想将calot连续三天或二天的数据统计出来得出以下结果
07-11817 6 (6-8三天的合计数)
07-11818 4 (19-20三天的合计数)
07-11817 3 (28-29三天的合计数)
empid workdate calot07-11817 2008-8-6 2.0
07-11817 2008-8-7 1.5
07-11817 2008-8-8 2.5
07-11817 2008-8-15 2.0
07-11817 2008-8-19 1.5
07-11818 2008-8-20 0.5
07-11818 2008-8-21 3.0
07-11819 2008-8-26 2.5
07-11819 2008-8-28 2.0
07-11819 2008-8-29 0.5 我想将calot连续三天或二天的数据统计出来得出以下结果
07-11817 6 (6-8三天的合计数)
07-11818 4 (19-20三天的合计数)
07-11817 3 (28-29三天的合计数)
SQL> create table a (empid varchar2(10),workdate date,calot number);Table createdSQL> desc a;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPID VARCHAR2(10) Y
WORKDATE DATE Y
CALOT NUMBER Y SQL> select * from A;EMPID WORKDATE CALOT
---------- ----------- ----------
07-11817 2008-8-6 2
07-11817 2008-8-7 1.5
07-11817 2008-8-8 2.5
07-11817 2008-8-15 2
07-11817 2008-8-19 1.5
07-11818 2008-8-20 0.5
07-11818 2008-8-21 3
07-11819 2008-8-26 2.5
07-11819 2008-8-28 2
07-11819 2008-8-29 0.510 rows selectedSQL>
SQL> select empid,sum(calot)
2 from
3 (
4 SELECT empid,workdate,calot
5 FROM (SELECT t2.*, COUNT (1) OVER (PARTITION BY empid, rn) cnt
6 FROM (SELECT t1.*, t1.workdate - ROWNUM rn
7 FROM (select * from A order by empid,workdate)t1) t2)
8 WHERE cnt >= 2
9 )t
10 group by empid
11 /EMPID SUM(CALOT)
---------- ----------
07-11817 6
07-11818 3.5
07-11819 2.5SQL>
--Smile
select empid,
nvl((select calot from AA where aa.worddate=bb.worddate and aa.empid=bb.empid),0)+
nvl((select calot from AA where aa.worddate=bb.tt1 and aa.empid=bb.empid),0)+
nvl((select calot from AA where aa.worddate=bb.tt2 and aa.empid=bb.empid),0)
from
(select * from
(select empid,worddate,
lead(worddate,1)over(partition by empid order by worddate) tt1,
lead(worddate,2)over(partition by empid order by worddate) tt2
from aa) kk
where kk.tt1 is not null
and (kk.tt1-1=kk.worddate and (kk.tt1=kk.tt2-1 or tt2 is null))) bb
select empid,sum(calot)
from
(
SELECT t1.*, COUNT (1) OVER (PARTITION BY empid, rn) cnt
FROM (select A.*,workdate - ROWNUM rn from A order by empid,workdate)t1
)t
where t.cnt >=2
group by empid