现在有在职员工 trz表 和 离职员工 tlz表 ,两个表均有frzrq(入职日期),flzrq(离职日期)。
这个语句是求今日的在职人员:
select sum(num)
from (select nvl(count(*), 0) as num
from trz t
where frzrq <= '2011-11-01'
and flzrq >= '2011-11-01'
union all
select nvl(count(*), 0) as num
from tlz t
where frzrq <= '2011-11-01'
and flzrq >= '2011-11-01'
) t
现要做一个视图v_zz,取每日的在职人员,格式为
日期 在职人数 2011-01-01 10
2011-01-02 55
2011-01-03 51
2011-01-04 56
2011-01-05 54请问各位大侠应该怎么实现?
SELECT RQ,SUM(NUM)
FROM (SELECT TRUNC(FRZRQ) RQ,NVL(COUNT(*), 0) AS NUM
FROM TRZ T
WHERE FRZRQ <= '2011-11-01'
AND FLZRQ >= '2011-01-01'
GROUP BY TRUN(FRZRQ)
UNION ALL
SELECT TRUNC(FLZRQ) RQ,NVL(COUNT(*), 0) AS NUM
FROM TLZ T
WHERE FRZRQ <= '2011-11-01'
AND FLZRQ >= '2011-01-01'
GROUP BY TRUN(FLZRQ)
) T
GROUP BY RQ;
select dt,
(select count(1)
from (select * from trz
union
select * from tlz) B
where to_char(dt,'yyyy-mm-dd') between B.frzrq and B.flzrq) ct
from
(select to_date('1950-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') + rownum - 1 dt
from dual
connect by rownum < 36500) A
);强烈建议楼主不要使用这种视图,因为这种统计的速度非常慢,楼主可以自己测试下。
FROM (SELECT T.DATE,
CASE
WHEN T.DATE BETWEEN A.FRZRQ AND A.FLZRQ THEN
1
ELSE
0
END NUM
FROM DATE_TABLE T,
(SELECT FRZRQ, FLZRQ
FROM TRZ T
UNION ALL
SELECT FRZRQ, FLZRQ FROM TLZ T) A)
GROUP BY A.DATE