我有一个表是这样的 xm 姓名 hlsj 核录时间 zt 状态(0 在职,1离职)
id xm hlsj zt 1 xm1 2010-10-11 0 2 xm2 2010-10-11 1 3 xm3 2010-11-11 0 4 xm4 2010-11-11 1 5 xm5 2010-12-11 0 6 xm6 2010-12-11 1 7 xm7 2011-10-11 0 8 xm8 2011-10-11 1 9 xm9 2011-11-11 0 10 xm10 2011-11-11 1 11 xm11 2011-12-11 0 12 xm12 2011-12-11 1我想统计出这样的结果(一)按月份统计 月份 在职人数 历史人数 2010-10 1 2 2010-11 1 2 2010-12 1 2 2011-10 1 2 2011-11 1 2 2011-12 1 2(二)按年份统计 年份 在职人数 历史人数 2010 3 6 2011 3 6
id xm hlsj zt 1 xm1 2010-10-11 0 2 xm2 2010-10-11 1 3 xm3 2010-11-11 0 4 xm4 2010-11-11 1 5 xm5 2010-12-11 0 6 xm6 2010-12-11 1 7 xm7 2011-10-11 0 8 xm8 2011-10-11 1 9 xm9 2011-11-11 0 10 xm10 2011-11-11 1 11 xm11 2011-12-11 0 12 xm12 2011-12-11 1我想统计出这样的结果(一)按月份统计 月份 在职人数 历史人数 2010-10 1 2 2010-11 1 2 2010-12 1 2 2011-10 1 2 2011-11 1 2 2011-12 1 2(二)按年份统计 年份 在职人数 历史人数 2010 3 6 2011 3 6
--假设hlsj字段是date型的(一)按月份:
select to_char(hlsj,'yyyy-mm')月份,sum(decode(zt,'1',1,0))在职人数,sum(decode(zt,'0',1,0)) 历史人数
from 表 group by to_char(hlsj,'yyyy-mm');(二)按年份:
select to_char(hlsj,'yyyy')年份,sum(decode(zt,'1',1,0))在职人数,sum(decode(zt,'0',1,0)) 历史人数
from 表 group by to_char(hlsj,'yyyy');
1
select to_char(hlsj,'yyyy-mm-dd') "month",
sum(decode(zt,1,1,0)) "离职人数",sum(decode(zt,0,1,0)) "在职人数"
from tb
group by to_char(hlsj,'yyyy-mm-dd')2
select to_char(hlsj,'yyyy') "year",
sum(decode(zt,1,1,0)) "离职人数",sum(decode(zt,0,1,0)) "在职人数"
from tb
group by to_char(hlsj,'yyyy')
WITH tab AS(
SELECT 1 id, 'xm1' xm, To_Date('2010-10-11','yyyy-mm-dd') hlsj, '0' zt FROM dual UNION ALL
select 2, 'xm2', To_Date('2010-10-11','yyyy-mm-dd'), '1' FROM dual UNION ALL
select 3, 'xm3', To_Date('2010-11-11','yyyy-mm-dd'), '0' FROM dual UNION ALL
SELECT 4, 'xm4', To_Date('2010-11-11','yyyy-mm-dd'), '1' FROM dual UNION ALL
select 5, 'xm5', To_Date('2010-12-11','yyyy-mm-dd'), '0' FROM dual UNION ALL
SELECT 6, 'xm6', To_Date('2010-12-11','yyyy-mm-dd'), '1' FROM dual UNION ALL
SELECT 7, 'xm7', To_Date('2011-10-11','yyyy-mm-dd'), '0' FROM dual UNION ALL
select 8, 'xm8', To_Date('2011-10-11','yyyy-mm-dd'), '1' FROM dual UNION ALL
select 9, 'xm9', To_Date('2011-11-11','yyyy-mm-dd'), '0' FROM dual UNION ALL
select 10,'xm10',To_Date('2011-11-11','yyyy-mm-dd'), '1' FROM dual UNION ALL
select 11,'xm11',To_Date('2011-12-11','yyyy-mm-dd'),'0' FROM dual UNION ALL
select 12,'xm12',To_Date('2011-12-11','yyyy-mm-dd'),'1' FROM dual
)
select to_char(hlsj,'yyyy-mm')月份,sum(decode(zt,'1',1,0))在职人数,sum(decode(zt,'0',1,0))离职人数,sum(1) 历史人数
from tab group by to_char(hlsj,'yyyy-mm');月份 在职人数 离职人数 历史人数
--------------------------------------------
2010-10 1 1 2
2010-11 1 1 2
2010-12 1 1 2
2011-10 1 1 2
2011-11 1 1 2
2011-12 1 1 2select to_char(hlsj,'yyyy')年份,sum(decode(zt,'1',1,0))在职人数,sum(decode(zt,'0',1,0))离职人数,sum(1) 历史人数
from tab group by to_char(hlsj,'yyyy');年份 在职人数 离职人数 历史人数
---------------------------------------
2010 3 3 6
2011 3 3 6