各位大虾:
如果我知道了人员的 【任职日期】 和【离职日期】 然后判断 某一月份的在职人数呢? 比如 1月份人数 30 二月份人数 33人 。。 做一个趋势列表 比如:已知列表
任职日期 离职日期
1998-04-01
2006-12-29
2005-01-25
2005-03-31
2008-07-01
2005-08-01
2004-08-09
2011-01-05
2005-05-25
2005-09-23
2005-05-23
2005-05-13
2008-02-25
2008-11-10
2008-07-28
2008-12-17
2009-02-23
2009-04-02
2009-04-15
2009-07-23
2010-12-06
2009-08-31
2009-09-14
2009-12-08
2010-01-15
2010-01-28
2010-02-20
2010-05-04
2008-07-08 2011-01-12
2005-04-25
2010-07-08 2011-03-31
2010-11-02
2010-08-24
2011-01-01
2005-09-19
2005-07-01
2009-11-10
2010-08-24
2003-04-30
2005-04-11
2009-07-01
2011-02-01
2010-12-20
2010-03-19 2011-01-19
2010-11-18
2008-05-19 2010-11-15
2006-04-06
2009-03-25
2009-09-02
2010-03-08
2010-07-28
1998-03-18
2011-04-01
2011-01-05
2011-01-11
2011-01-04
2010-12-01 2011-01-03
2011-03-14
2011-03-08
2011-03-15 要求效果
年度 月度 人数
2010 01 3
2010 02 6
2010 05 3
2010 06 6
2010 07 3
2010 09 3
2010 11 12
2011 01 9
2011 03 6
望高手能解答!
如果我知道了人员的 【任职日期】 和【离职日期】 然后判断 某一月份的在职人数呢? 比如 1月份人数 30 二月份人数 33人 。。 做一个趋势列表 比如:已知列表
任职日期 离职日期
1998-04-01
2006-12-29
2005-01-25
2005-03-31
2008-07-01
2005-08-01
2004-08-09
2011-01-05
2005-05-25
2005-09-23
2005-05-23
2005-05-13
2008-02-25
2008-11-10
2008-07-28
2008-12-17
2009-02-23
2009-04-02
2009-04-15
2009-07-23
2010-12-06
2009-08-31
2009-09-14
2009-12-08
2010-01-15
2010-01-28
2010-02-20
2010-05-04
2008-07-08 2011-01-12
2005-04-25
2010-07-08 2011-03-31
2010-11-02
2010-08-24
2011-01-01
2005-09-19
2005-07-01
2009-11-10
2010-08-24
2003-04-30
2005-04-11
2009-07-01
2011-02-01
2010-12-20
2010-03-19 2011-01-19
2010-11-18
2008-05-19 2010-11-15
2006-04-06
2009-03-25
2009-09-02
2010-03-08
2010-07-28
1998-03-18
2011-04-01
2011-01-05
2011-01-11
2011-01-04
2010-12-01 2011-01-03
2011-03-14
2011-03-08
2011-03-15 要求效果
年度 月度 人数
2010 01 3
2010 02 6
2010 05 3
2010 06 6
2010 07 3
2010 09 3
2010 11 12
2011 01 9
2011 03 6
望高手能解答!
写个cursor查询原表并遍历,对fetch出的每一行,
update 目标表 set 人数 = 人数+1 where 年月>=入职日期;
update 目标表 set 人数 = 人数-1 where 年月>=离职日期;
select count(*) from 人员 where to_date('2011-05','yyyy-mm')>=【任职日期】 and to_date('2011-05','yyyy-mm')<=【离职日期】
如果要查多个的话,可以用union在代码里面拼sql
Name Type Nullable Default Comments
-------- ------ -------- ------- --------
WORKNO NUMBER Y
HIREDATE DATE Y
FIRDATE DATE Y
SQL>
SQL> WITH tt AS
2 (SELECT to_date('2010' || lpad(rownum, 2, '0'), 'yyyymm') tdate
3 FROM dual
4 CONNECT BY rownum <= 12)
5 SELECT to_char(tdate, 'yyyy') YEAR, to_char(tdate, 'mm') MONTH, COUNT(*)
6 FROM ee, tt
7 WHERE last_day(tt.tdate) BETWEEN ee.hiredate AND
8 nvl(ee.firdate, DATE '3000-01-01')
9 GROUP BY tdate
10 ORDER BY tdate;
YEAR MONTH COUNT(*)
---- ----- ----------
2010 01 37
2010 02 38
2010 03 40
2010 04 40
2010 05 41
2010 06 41
2010 07 43
2010 08 45
2010 09 45
2010 10 45
2010 11 46
2010 12 49
12 rows selected
SQL>
(SELECT to_date('2001' || lpad(rownum, 2, '0'), 'yyyymm') tdate FROM dual CONNECT BY rownum <= 12)
SELECT to_char(tdate, 'yyyy') YEAR, to_char(tdate, 'mm') MONTH, COUNT(*)
FROM ee, tt
WHERE last_day(tt.tdate) BETWEEN ee.hiredate AND
nvl(ee.firdate, DATE '3000-01-01')
GROUP BY tdate
ORDER BY tdate;能不能换种写发 不要用with as 这种 ,因为我是用别的查询分析器,不支持这种with。。 感谢!!
SELECT to_char(tdate, 'yyyy') YEAR, to_char(tdate, 'mm') MONTH, COUNT(*)
FROM ee, (SELECT to_date('2001' || lpad(rownum, 2, '0'), 'yyyymm') tdate
FROM dual CONNECT BY rownum <= 12) tt
WHERE last_day(tt.tdate) BETWEEN ee.hiredate AND
nvl(ee.firdate, DATE '3000-01-01')
GROUP BY tdate
ORDER BY tdate;