56,673
社区成员
发帖
与我相关
我的任务
分享
--输入年份
DECLARE @createtime DATETIME = '2016'
--测试数据
;WITH 部门表a (depid,depname)
as
(
select 1,'研发部' union all
SELECT 2,'产品部'
),部门员工表b(usersid,depid,createtime)AS(
select 1,1,'2016-01-01 00:00:00' union all
select 2,2,'2016-02-01 00:00:00' union all
select 3,1,'2016-03-01 00:00:00' union all
select 4,1,'2015-01-01 00:00:00' union all
select 5,2,'2015-11-01 00:00:00'
)
--测试数据结束
SELECT 部门表a.depname ,
( SELECT COUNT(1)
FROM 部门员工表b
WHERE 部门表a.depid = 部门员工表b.depid
AND YEAR(@createtime)=YEAR(createtime)
) AS 员工数,
( SELECT COUNT(1)
FROM 部门员工表b
WHERE 部门表a.depid = 部门员工表b.depid
AND YEAR(@createtime)=date_add(createtime,interval -1 year)
) AS 去年员工数
FROM 部门表a
--输入年份
DECLARE @createtime DATETIME = '2016'
--测试数据
;WITH 部门表a (depid,depname)
as
(
select 1,'研发部' union all
SELECT 2,'产品部'
),部门员工表b(usersid,depid,createtime)AS(
select 1,1,'2016-01-01' union all
select 2,2,'2016-02-01' union all
select 3,1,'2016-03-01' union all
select 4,1,'2015-01-01' union all
select 5,2,'2015-11-01'
)
--测试数据结束
SELECT 部门表a.depname ,
( SELECT COUNT(1)
FROM 部门员工表b
WHERE 部门表a.depid = 部门员工表b.depid
AND YEAR(@createtime)=YEAR(createtime)
) AS 员工数
FROM 部门表a