with t as ( select to_date('2015-01-26','yyyy-mm-dd') A,to_date('2015-2-14','yyyy-mm-dd') B FROM DUAL UNION ALL select to_date('2014-12-30','yyyy-mm-dd') A,to_date('2015-2-12','yyyy-mm-dd') B FROM DUAL UNION ALL select to_date('2014-12-30','yyyy-mm-dd') A,to_date('2015-1-14','yyyy-mm-dd') B FROM DUAL UNION ALL select to_date('2015-1-5','yyyy-mm-dd') A,to_date('2015-1-25','yyyy-mm-dd') B FROM DUAL UNION ALL select to_date('2014-12-29','yyyy-mm-dd') A,to_date('2015-1-26','yyyy-mm-dd') B FROM DUAL UNION ALL select to_date('2015-01-26','yyyy-mm-dd') A,null B FROM DUAL UNION ALL select to_date('2014-12-20','yyyy-mm-dd') A,null B FROM DUAL ) select t1.A,T1.B,COUNT(T2.DT) from t t1,(select to_date('20150101', 'yyyymmdd') + level - 1 dt from dual connect by to_char(to_date('20150101', 'yyyymmdd') + level - 1, 'MM') = '01') t2 where T2.DT BETWEEN NVL(T1.A,T2.DT) AND NVL(T1.B,T2.DT) GROUP BY t1.A,T1.B;
select t1.ryrq,T1.cyrq,COUNT(T2.DT) from sq t1, (select to_date('20150401', 'yyyymmdd') + level - 1 dt from dual connect by to_char(to_date('20150101', 'yyyymmdd') + level - 1, 'MM') = '01') t2 where T2.DT BETWEEN NVL(T1.ryrq,T2.DT) AND NVL(T1.cyrq,T2.DT) and t1.zyh = '06396' or t1.zyh = '06360' or t1.zyh = '06369' or t1.zyh = '06367' or t1.zyh = '06368' or t1.zyh = '06379' GROUP BY t1.ryrq,T1.cyrq;我是这样写的,但不是每一个都计算正确
with t as
(
select to_date('2015-01-26','yyyy-mm-dd') A,to_date('2015-2-14','yyyy-mm-dd') B FROM DUAL UNION ALL
select to_date('2014-12-30','yyyy-mm-dd') A,to_date('2015-2-12','yyyy-mm-dd') B FROM DUAL UNION ALL
select to_date('2014-12-30','yyyy-mm-dd') A,to_date('2015-1-14','yyyy-mm-dd') B FROM DUAL UNION ALL
select to_date('2015-1-5','yyyy-mm-dd') A,to_date('2015-1-25','yyyy-mm-dd') B FROM DUAL UNION ALL
select to_date('2014-12-29','yyyy-mm-dd') A,to_date('2015-1-26','yyyy-mm-dd') B FROM DUAL UNION ALL
select to_date('2015-01-26','yyyy-mm-dd') A,null B FROM DUAL UNION ALL
select to_date('2014-12-20','yyyy-mm-dd') A,null B FROM DUAL
)
select t1.A,T1.B,COUNT(T2.DT) from t t1,(select to_date('20150101', 'yyyymmdd') + level - 1 dt
from dual
connect by to_char(to_date('20150101', 'yyyymmdd') + level - 1, 'MM') = '01') t2
where T2.DT BETWEEN NVL(T1.A,T2.DT) AND NVL(T1.B,T2.DT)
GROUP BY t1.A,T1.B;
(select to_date('20150401', 'yyyymmdd') + level - 1 dt from dual connect by to_char(to_date('20150101', 'yyyymmdd') + level - 1, 'MM') = '01') t2
where T2.DT BETWEEN NVL(T1.ryrq,T2.DT) AND NVL(T1.cyrq,T2.DT)
and t1.zyh = '06396' or t1.zyh = '06360' or t1.zyh = '06369' or t1.zyh = '06367' or t1.zyh = '06368' or t1.zyh = '06379'
GROUP BY t1.ryrq,T1.cyrq;我是这样写的,但不是每一个都计算正确