统计一年中每个月度新增入职和离职人数

统计一年中每个月份新增入职和离职人数？
数据库中字段
  时间 状态  
2012-07-05 入职
2012-07-08 入职
2012-05-03 离职
统计结果显示

  时间 入职人数 离职人数
 2012-07 2 0
 2012-05 0 1
该如何查询？

[解决办法]

SQL code

select 时间 , sum(case when 状态='入职' then 1 else 0  end ) as '入职数',                           sum(case when 状态='离职' then 1 else 0  end ) as '离职数'from 表group by  时间
[解决办法]
SQL code
create table tb(时间 datetime,状态 nvarchar(2))insert into tb select '2012-07-05', '入职' union allselect '2012-07-08', '入职' union allselect '2012-05-03', '离职'select CONVERT (varchar(7),时间,120) 时间 , sum(case when 状态='入职' then 1 else 0  end )  入职人数,sum(case when 状态='离职' then 1 else 0  end )  离职人数from tbgroup by  CONVERT (varchar(7),时间,120)
[解决办法]
SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (时间 datetime,状态 nvarchar(4))insert into [TB]select '2012-07-05','入职' union allselect '2012-07-08','入职' union allselect '2012-05-03','离职'select * from [TB]SELECT CONVERT(VARCHAR(7),时间,120) AS 时间,SUM(CASE WHEN 状态 ='入职' THEN 1 ELSE 0 END ) AS '入职人数',SUM(CASE WHEN 状态 ='离职' THEN 1 ELSE 0 END ) AS '离职人数'FROM dbo.TBGROUP BY CONVERT(VARCHAR(7),时间,120)/*时间      入职人数        离职人数------- ----------- -----------2012-05 0           12012-07 2           0(2 行受影响)*/
[解决办法]
select '2012-'+convert(varchar(3),MONTH(时间)) as '年月', SUM(case when 状态='入职' then 1 else 0 end)as '入职人数',
sum(case when 状态='离职'  then 1 else 0 end) as '离职人数' from 表名 group by month(时间)

[解决办法]
SQL code
--测试数据IF OBJECT_ID('[TB]') IS  NOT NULLDROP TABLE [TB]GOCREATE TABLE [TB](DATE datetime,STATUS nvarchar(4))INSERT INTO [TB]SELECT '2012-07-05','入职' UNION ALLSELECT '2012-07-08','入职' UNION ALLSELECT '2012-05-03','离职'--SELECT * FROM TB--开始查询SELECT CONVERT(VARCHAR(7),DATE,23) AS 时间,SUM(CASE WHEN STATUS ='入职' THEN 1 ELSE 0 END ) AS '入职人数',SUM(CASE WHEN STATUS ='离职' THEN 1 ELSE 0 END ) AS '离职人数'FROM TBGROUP BY CONVERT(VARCHAR(7),DATE,23)--查询结果/*DATA      入职人数        离职人数------- ----------- -----------2012-05 0           12012-07 2           0(2 行受影响)*/