34,593
社区成员
发帖
与我相关
我的任务
分享
--测试数据
IF OBJECT_ID('[TB]') IS NOT NULL
DROP TABLE [TB]
GO
CREATE TABLE [TB](DATE datetime,STATUS nvarchar(4))
INSERT INTO [TB]
SELECT '2012-07-05','入职' UNION ALL
SELECT '2012-07-08','入职' UNION ALL
SELECT '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 TB
GROUP BY CONVERT(VARCHAR(7),DATE,23)
--查询结果
/*
DATA 入职人数 离职人数
------- ----------- -----------
2012-05 0 1
2012-07 2 0
(2 行受影响)
*/
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (时间 datetime,状态 nvarchar(4))
insert into [TB]
select '2012-07-05','入职' union all
select '2012-07-08','入职' union all
select '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.TB
GROUP BY CONVERT(VARCHAR(7),时间,120)
/*
时间 入职人数 离职人数
------- ----------- -----------
2012-05 0 1
2012-07 2 0
(2 行受影响)
*/
create table tb
(时间 datetime,状态 nvarchar(2))
insert into tb
select '2012-07-05', '入职' union all
select '2012-07-08', '入职' union all
select '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 tb
group by CONVERT (varchar(7),时间,120)
select 时间 , sum(case when 状态='入职' then 1 else 0 end ) as '入职数',
sum(case when 状态='离职' then 1 else 0 end ) as '离职数'
from 表
group by 时间