如有一个表
工号 拉号 入厂日期 离职日期
01 A1 2010/10/01 2010/10/10
02 A1 2010/10/01 2010/10/05
03 A2 2010/10/02 2010/10/05
有一个WEB页面,输入日期范围2010/10/02—2010/10/06
怎样用SQL语句显示以下资料
日期 拉号 员工总数
2010/10/02 A1 2
2010/10/02 A2 1
2010/10/03 A1 2
2010/10/03 A2 1
2010/10/04 A1 2
2010/10/04 A2 1
2010/10/05 A1 2
2010/10/05 A2 1
2010/10/06 A1 1
2010/10/06 A2 0
工号 拉号 入厂日期 离职日期
01 A1 2010/10/01 2010/10/10
02 A1 2010/10/01 2010/10/05
03 A2 2010/10/02 2010/10/05
有一个WEB页面,输入日期范围2010/10/02—2010/10/06
怎样用SQL语句显示以下资料
日期 拉号 员工总数
2010/10/02 A1 2
2010/10/02 A2 1
2010/10/03 A1 2
2010/10/03 A2 1
2010/10/04 A1 2
2010/10/04 A2 1
2010/10/05 A1 2
2010/10/05 A2 1
2010/10/06 A1 1
2010/10/06 A2 0
WORKNUM GROUPNUM HIREDATE FIREDATE
---------- ---------- ----------- -----------
01 A1 2010-10-1 2010-10-10
02 A1 2010-10-1 2010-10-5
03 A2 2010-10-2 2010-10-5
SQL>
SQL> select b.caldate, b.groupnum, count(a.groupnum)
2 from tbl_cal a,
3 (select distinct caldate, groupnum
4 from tbl_cal,
5 (SELECT to_date('2010/10/02', 'yyyy/mm/dd') + rownum - 1 caldate
6 from dual
7 connect by rownum <= (to_date('2010/10/06', 'yyyy/mm/dd') -
8 to_date('2010/10/02', 'yyyy/mm/dd')))) b
9 where b.caldate between a.hiredate(+) and a.firedate(+)
10 and b.groupnum = a.groupnum(+)
11 group by b.caldate, b.groupnum
12 ;
CALDATE GROUPNUM COUNT(A.GROUPNUM)
----------- ---------- -----------------
2010-10-2 A1 2
2010-10-2 A2 1
2010-10-3 A1 2
2010-10-3 A2 1
2010-10-4 A1 2
2010-10-4 A2 1
2010-10-5 A1 2
2010-10-5 A2 1
2010-10-6 A1 1
2010-10-6 A2 0
10 rows selected
SQL>
WHERE t.leavetime>SYSDATE
GROUP BY trunc(SYSDATE,'dd'),t.lahao;
WITH tab AS(
SELECT '01'工号,'A1' 拉号,To_Date('2010/10/01','yyyy/mm/dd') 入厂日期,To_Date('2010/10/10','yyyy/mm/dd')离职日期 FROM dual
UNION ALL
SELECT '02','A1',To_Date('2010/10/01','yyyy/mm/dd'),To_Date('2010/10/05','yyyy/mm/dd') FROM DUAL
UNION ALL
SELECT '03','A2',To_Date('2010/10/02','yyyy/mm/dd'),To_Date('2010/10/05','yyyy/mm/dd') FROM DUAL
)
SELECT To_Char(b.dt,'yyyy/mm/dd') 日期,b.拉号,Count(a.拉号)员工总数
FROM tab a,(select DISTINCT 拉号,dt
FROM TAB ,(
SELECT To_Date('2010/10/02','yyyy/mm/dd')+LEVEL-1 dt
FROM dual
CONNECT BY LEVEL<= To_Date('2010/10/06','yyyy/mm/dd') - To_Date('2010/10/02','yyyy/mm/dd')+1
)
)b
WHERE b.拉号=a.拉号(+) AND b.dt BETWEEN a.入厂日期(+) AND a.离职日期(+)
GROUP BY b.dt,b.拉号
结果:
日期 拉号 员工总数
----------------------
2010/10/02 A1 2
2010/10/02 A2 1
2010/10/03 A1 2
2010/10/03 A2 1
2010/10/04 A1 2
2010/10/04 A2 1
2010/10/05 A1 2
2010/10/05 A2 1
2010/10/06 A1 1
2010/10/06 A2 0
from test a,
(select distinct 日期, 拉号
from test,
(SELECT to_date('2010/10/02', 'yyyy/mm/dd') + rownum - 1 日期
from dual
connect by rownum <= (to_date('2010/10/06', 'yyyy/mm/dd') - to_date('2010/10/02', 'yyyy/mm/dd'))+1
)
) b
where b.日期 between a.入厂日期(+) and a.离职日期(+)
and b.拉号= a.拉号(+)
group by b.日期, b.拉号
order by 1,2;