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');
select count(*), to_char(p.admission_date_time, 'yyyy-mm') from pat_visit p group by to_char(p.admission_date_time, 'yyyy-mm') --不可以酱紫?
题目意思,我觉得是 每天的量加一个汇总。 记得好像 roll up over 可以这样了
是Oracle数据库吧,试试下面的: 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 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;
用rollup的写法 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);
每天的用group算出来,
总的 再用一条语句
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');
select count(*), to_char(p.admission_date_time, 'yyyy-mm')
from pat_visit p
group by to_char(p.admission_date_time, 'yyyy-mm')
--不可以酱紫?
题目意思,我觉得是 每天的量加一个汇总。 记得好像 roll up over 可以这样了
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 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 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);