数据状态如下,统计每月人数:
人员编码 姓名 到职日期 离职日期
1 张三 2008-1-1
2 李四 2008-1-1
3 王五 2008-1-1 2009-7-1
4 赵六 2009-5-1 2009-7-1insert into test (id,name,indutydate,enddate)
select '1','张三','2008-1-1','' from dual
union all select '2','李四','2008-1-1','' from dual
union all select '3','王五','2008-1-1','2009-7-1' from dual
union all select '4','赵六','2008-1-1','2009-8-1' from dual常规方法是输入参数,然后查询返回结果:
例如:统计8月份人数,输入2009-8-31, 结果为2
select count(*) from test where ( enddate is null or enddate> '2009-8-31');
统计7月份人数,输入2009-7-31,结果为3
select count(*) from test where ( enddate is null or enddate> '2009-7-31')现在希望数据变化如下所示:
年份 月份 人数
2009 8 2
2009 7 2
条件即为: enddate is null or enddate> 输入参数
人员编码 姓名 到职日期 离职日期
1 张三 2008-1-1
2 李四 2008-1-1
3 王五 2008-1-1 2009-7-1
4 赵六 2009-5-1 2009-7-1insert into test (id,name,indutydate,enddate)
select '1','张三','2008-1-1','' from dual
union all select '2','李四','2008-1-1','' from dual
union all select '3','王五','2008-1-1','2009-7-1' from dual
union all select '4','赵六','2008-1-1','2009-8-1' from dual常规方法是输入参数,然后查询返回结果:
例如:统计8月份人数,输入2009-8-31, 结果为2
select count(*) from test where ( enddate is null or enddate> '2009-8-31');
统计7月份人数,输入2009-7-31,结果为3
select count(*) from test where ( enddate is null or enddate> '2009-7-31')现在希望数据变化如下所示:
年份 月份 人数
2009 8 2
2009 7 2
条件即为: enddate is null or enddate> 输入参数
create procedure pro_test(cdate in date)as
num number;
begin
select count(1) into num from test
where enddate is null not enddate>cdate;
dbms_output.put_line(to_char(cdate,'YYYY MM '||num);
end pro_test;
每个月的在职人数
select t.m,count(1) from(
select add_months(to_date('2008-1','YYYY-MM' ),rownum-1) m from dual
connect by add_months(to_date('2008-1','YYYY-MM' ),rownum-1)<=sysdate) t
left join test t1
on t.m>=trunc(to_date(t1.indutydate,'YYYY-MM-DD'),'MM')
and (t.m<trunc(to_date(t1.enddate,'YYYY-MM-DD'),'MM')
or t1.enddate is null)
group by t.m
order by t.m
我的真实数据比这复杂一些,
人员编码 姓名 到职日期 离职日期 人员分类
1 张三 2008-1-1 农民工
2 李四 2008-1-1 农民工
3 王五 2008-1-1 2009-7-1 城镇工
4 赵六 2009-5-1 2009-7-1 城镇工
有很多类似“人员分类”的字段,需要按这些字段分别进行统计每月人数。
即:
统计期间 人数 人员分类
2008-01 2 农民工
2008-01 2 城镇工
2008-02 2 农民工
2008-02 2 城镇工
我自己试了半天,不是很理解这个语句,不知道如何改。
请再帮忙处理一下,非常感谢。
添加测试字段如下:
alter table test add tj varchar2(20)
update test set tj='农民工' where id =1 or id=2;
update test set tj='城镇工' where id =3 or id=4
另外有个地方要改一下:count(1)里面的1
select t.m,t1.tj,count(t1.name) from(
select add_months(to_date('2008-1','YYYY-MM' ),rownum-1) m from dual
connect by add_months(to_date('2008-1','YYYY-MM' ),rownum-1)<=sysdate) t
left join test t1
on t.m>=trunc(to_date(t1.indutydate,'YYYY-MM-DD'),'MM')
and (t.m<trunc(to_date(t1.enddate,'YYYY-MM-DD'),'MM')
or t1.enddate is null)
group by t.m,t1.tj
order by t.m,t1.tj
select add_months(to_date('2008-1','YYYY-MM' ),rownum-1) m from dual
connect by add_months(to_date('2008-1','YYYY-MM' ),rownum-1)<=sysdate
这是为了生成你要统计的月份列表,以2008-1为例,产生2008-1到2009-8的记录
然后将其关联你要统计的表,以时间为条件关联
关联结果里每个要统计的月份会对应若干条符合条件的记录
分组统计数目,得到你要的结果
还有个需求,统计月平均人数,规则是每天人数除以当月天数,
我之前到思路是统计每月每人出现的次数,次数求和/当月天数。发现数据多了后,统计效率也不高。
代码如下:
SELECT psncode,psnname,indutydate,outdutydate,
( CASE when bd_psndoc.indutydate<'2009-01-01' AND bd_psndoc.outdutydate<'2009-01-01' then 0
WHEN bd_psndoc.indutydate<'2009-01-01' and ( bd_psndoc.outdutydate is null or bd_psndoc.outdutydate = '') THEN 31
WHEN (bd_psndoc.indutydate>'2009-01-01' AND bd_psndoc.indutydate<'2009-01-31' ) AND ( bd_psndoc.outdutydate is null or bd_psndoc.outdutydate = '') THEN
datediff(DD, bd_psndoc.indutydate,'2009-01-31' )
WHEN (bd_psndoc.indutydate>'2009-01-01' AND bd_psndoc.indutydate<'2009-01-31') AND (bd_psndoc.outdutydate>'2009-01-01' and bd_psndoc.outdutydate<'2009-01-31')
THEN datediff(DD, bd_psndoc.indutydate, bd_psndoc.outdutydate)
WHEN bd_psndoc.indutydate<'2009-01-01' AND (bd_psndoc.outdutydate>'2009-01-01' and bd_psndoc.outdutydate<'2009-01-31') THEN
datediff(DD, '2009-01-01', bd_psndoc.outdutydate)
ELSE 99
END ) rsFROM bd_psndoc现在想套用每月人数的思路,统计每天的日期,。
select to_date('2008-3-1','YYYY-MM-DD') m from dual connect by to_date('2008-3-1','YYYY-MM-DD')<=to_date('2008-4-1','YYYY-MM-DD')
发现统计不对,我想计算出3月每天的日,即:2009-3-1,2009-3-2 ········· 到4月。
不知道该如何写啊。