17,090
社区成员
发帖
与我相关
我的任务
分享
select B.RQ,COUNT(1)
from pat_visit A,(select to_date('20140701','yyyymmdd')+rownum-1 RQ from dual connect by rownum<=31)B
WHERE TRUNC(A.admission_date_time)<=B.RQ AND TRUNC(A.discharge_date_time)>=B.RQ
GROUP BY ROLLUP(B.RQ);
select B.RQ,COUNT(1)
from pat_visit A,(select to_date('20140701','yyyymmdd')+rownum-1 RQ from dual connect by rownum<=31)B
WHERE TRUNC(A.admission_date_time)<=B.RQ AND TRUNC(A.discharge_date_time)>=B.RQ
GROUP BY B.RQ;
计算月度总和
select sum(COUNT(1))
from pat_visit A,(select to_date('20140701','yyyymmdd')+rownum-1 RQ from dual connect by rownum<=31)B
WHERE TRUNC(A.admission_date_time)<=B.RQ AND TRUNC(A.discharge_date_time)>=B.RQ
GROUP BY B.RQ;
select to_date('2014-07-01', 'yyyy-MM-dd') as rq ,count(*)
from pat_visit p
group by to_date('2014-07-01', 'yyyy-MM-dd')
with rollup
select count(*), to_char(p.admission_date_time, 'yyyy-mm')
from pat_visit p
group by to_char(p.admission_date_time, 'yyyy-mm')
--不可以酱紫?
select sum(
least(to_date('2014-07-31', 'yyyy-MM-dd'),trunc(discharge_date_time))
- greatest(to_date('2014-07-01', 'yyyy-MM-dd'),trunc(admission_date_time))
+1
)
from pat_visit p
where trunc(p.admission_date_time) <= to_date('2014-07-31', 'yyyy-MM-dd')
and trunc(p.discharge_date_time) >= to_date('2014-07-01', 'yyyy-MM-dd');