with tb as( select '甲' 人名,to_date('2011-3-25','yyyy-mm-dd') 开始时间,2 工作天数,3 游戏天数 from dual union all select '乙',to_date('2011-3-26','yyyy-mm-dd'), 3, 2 from dual), tbt as( --联系时间表 select (select min(开始时间) from tb)+rownum-1 时间 from tb connect by rownum<( select max(开始时间)-min(开始时间)-1 from tb) ) select tbt.时间,count(*) from tb,tbt where tb.开始时间<=tbt.时间 group by tbt.时间 order by tbt.时间时间 COUNT(*) ---------- ---------- 2011-03-25 1 2011-03-26 2
with a as( select t1.ename,t1.sdate, t1.sdate+t1.workday as wkenddate, t1.sdate+t1.gameday as gmenddate, t1.sdate+(case when t1.workday>t1.gameday then t1.workday else t1.gameday end) as maxdate from t t1), b as(select min(a.sdate) as minsdate, max(maxdate) as maxsdate from a), c as(select b.minsdate + level -1 as everyday from b connect by level <=(select maxsdate-minsdate from b) ), d as( select a.ename,a.sdate, c.everyday, 1 as workday, 0 as gameday from c inner join a on c.everyday>=a.sdate and c.everyday<=a.wkenddate union all select a.ename,a.sdate, c.everyday, 0 as workday, 1 as gameday from c inner join a on c.everyday>=a.sdate and c.everyday<=a.gmenddate ) select d.everyday, sum(d.workday) as workday, sum(d.gameday) as gameday from d group by d.everyday order by d.everyday;
create table t( ename varchar2(20), sdate date, workday number(18,0), gameday number(18,0) );insert into t(ename,sdate,workday,gameday) values('甲',to_date('2011-3-25','yyyy-mm-dd'), 2, 3); insert into t(ename,sdate,workday,gameday) values('乙',to_date('2011-3-26','yyyy-mm-dd'), 3, 2);with a as( select t1.ename,t1.sdate, t1.sdate+t1.workday as wkenddate, t1.sdate+t1.gameday as gmenddate, t1.sdate+(case when t1.workday>t1.gameday then t1.workday else t1.gameday end) as maxdate from t t1), b as(select min(a.sdate) as minsdate, max(maxdate) as maxsdate from a), c as(select b.minsdate + level -1 as everyday from b connect by level <=(select maxsdate-minsdate from b) ), d as( select a.ename,a.sdate, c.everyday, 1 as workday, 0 as gameday from c inner join a on c.everyday>=a.sdate and c.everyday<=a.wkenddate union all select a.ename,a.sdate, c.everyday, 0 as workday, 1 as gameday from c inner join a on c.everyday>=a.sdate and c.everyday<=a.gmenddate ) select d.everyday, sum(d.workday) as workday, sum(d.gameday) as gameday from d group by d.everyday order by d.everyday;
-- 修正一下: create table t( ename varchar2(20), sdate date, workday number(18,0), gameday number(18,0) );insert into t(ename,sdate,workday,gameday) values('甲',to_date('2011-3-25','yyyy-mm-dd'), 2, 3); insert into t(ename,sdate,workday,gameday) values('乙',to_date('2011-3-26','yyyy-mm-dd'), 3, 2); insert into t(ename,sdate,workday,gameday) values('丙',to_date('2011-3-27','yyyy-mm-dd'), 0, 2); with a as( select t1.ename,t1.sdate,t1.workday, t1.gameday, t1.sdate+(case when t1.workday>0 then t1.workday-1 else 0 end) as wkenddate, t1.sdate+(case when t1.gameday>0 then t1.gameday-1 else 0 end) as gmenddate, t1.sdate+(case when t1.workday>t1.gameday then t1.workday else t1.gameday end) as maxdate from t t1), b as(select min(a.sdate) as minsdate, max(maxdate) as maxsdate from a), c as(select b.minsdate + level -1 as everyday from b connect by level <=(select maxsdate-minsdate from b) ), d as( select a.ename,a.sdate, c.everyday, (case when a.workday>0 then 1 else 0 end) as workday, 0 as gameday from c inner join a on c.everyday>=a.sdate and c.everyday<=a.wkenddate union all select a.ename,a.sdate, c.everyday, 0 as workday, (case when a.gameday>0 then 1 else 0 end) as gameday from c inner join a on c.everyday>=a.sdate and c.everyday<=a.gmenddate and a.gameday>0 ) select d.everyday, sum(d.workday) as workday, sum(d.gameday) as gameday from d group by d.everyday order by d.everyday;
with tb as(
select '甲' 人名,to_date('2011-3-25','yyyy-mm-dd') 开始时间,2 工作天数,3 游戏天数 from dual union all
select '乙',to_date('2011-3-26','yyyy-mm-dd'), 3, 2 from dual),
tbt as( --联系时间表
select (select min(开始时间) from tb)+rownum-1 时间
from tb connect by rownum<(
select max(开始时间)-min(开始时间)-1 from tb)
)
select tbt.时间,count(*)
from tb,tbt where tb.开始时间<=tbt.时间
group by tbt.时间
order by tbt.时间时间 COUNT(*)
---------- ----------
2011-03-25 1
2011-03-26 2
select t1.ename,t1.sdate,
t1.sdate+t1.workday as wkenddate,
t1.sdate+t1.gameday as gmenddate,
t1.sdate+(case when t1.workday>t1.gameday then t1.workday else t1.gameday end) as maxdate
from t t1),
b as(select min(a.sdate) as minsdate, max(maxdate) as maxsdate from a),
c as(select b.minsdate + level -1 as everyday from b connect by level <=(select maxsdate-minsdate from b) ),
d as(
select a.ename,a.sdate, c.everyday, 1 as workday, 0 as gameday
from c inner join a on c.everyday>=a.sdate and c.everyday<=a.wkenddate
union all
select a.ename,a.sdate, c.everyday, 0 as workday, 1 as gameday
from c inner join a on c.everyday>=a.sdate and c.everyday<=a.gmenddate )
select d.everyday, sum(d.workday) as workday, sum(d.gameday) as gameday
from d
group by d.everyday
order by d.everyday;
ename varchar2(20),
sdate date,
workday number(18,0),
gameday number(18,0)
);insert into t(ename,sdate,workday,gameday) values('甲',to_date('2011-3-25','yyyy-mm-dd'), 2, 3);
insert into t(ename,sdate,workday,gameday) values('乙',to_date('2011-3-26','yyyy-mm-dd'), 3, 2);with a as(
select t1.ename,t1.sdate,
t1.sdate+t1.workday as wkenddate,
t1.sdate+t1.gameday as gmenddate,
t1.sdate+(case when t1.workday>t1.gameday then t1.workday else t1.gameday end) as maxdate
from t t1),
b as(select min(a.sdate) as minsdate, max(maxdate) as maxsdate from a),
c as(select b.minsdate + level -1 as everyday from b connect by level <=(select maxsdate-minsdate from b) ),
d as(
select a.ename,a.sdate, c.everyday, 1 as workday, 0 as gameday
from c inner join a on c.everyday>=a.sdate and c.everyday<=a.wkenddate
union all
select a.ename,a.sdate, c.everyday, 0 as workday, 1 as gameday
from c inner join a on c.everyday>=a.sdate and c.everyday<=a.gmenddate )
select d.everyday, sum(d.workday) as workday, sum(d.gameday) as gameday
from d
group by d.everyday
order by d.everyday;
create table t(
ename varchar2(20),
sdate date,
workday number(18,0),
gameday number(18,0)
);insert into t(ename,sdate,workday,gameday) values('甲',to_date('2011-3-25','yyyy-mm-dd'), 2, 3);
insert into t(ename,sdate,workday,gameday) values('乙',to_date('2011-3-26','yyyy-mm-dd'), 3, 2);
insert into t(ename,sdate,workday,gameday) values('丙',to_date('2011-3-27','yyyy-mm-dd'), 0, 2);
with a as(
select t1.ename,t1.sdate,t1.workday, t1.gameday,
t1.sdate+(case when t1.workday>0 then t1.workday-1 else 0 end) as wkenddate,
t1.sdate+(case when t1.gameday>0 then t1.gameday-1 else 0 end) as gmenddate,
t1.sdate+(case when t1.workday>t1.gameday then t1.workday else t1.gameday end) as maxdate
from t t1),
b as(select min(a.sdate) as minsdate, max(maxdate) as maxsdate from a),
c as(select b.minsdate + level -1 as everyday from b connect by level <=(select maxsdate-minsdate from b) ),
d as(
select a.ename,a.sdate, c.everyday, (case when a.workday>0 then 1 else 0 end) as workday, 0 as gameday
from c inner join a on c.everyday>=a.sdate and c.everyday<=a.wkenddate
union all
select a.ename,a.sdate, c.everyday, 0 as workday, (case when a.gameday>0 then 1 else 0 end) as gameday
from c inner join a on c.everyday>=a.sdate and c.everyday<=a.gmenddate and a.gameday>0 )
select d.everyday, sum(d.workday) as workday, sum(d.gameday) as gameday
from d
group by d.everyday
order by d.everyday;