SQL 员工离职人数统计

[si12si12]

 CREATE TABLE EmpDismissType
(
    ID INT PRIMARY KEY,
    strType VARCHAR(40) NOT NULL
)
go
insert into EmpDismissType(ID,strType)
select 1,'试用期离职' union
select 2,'合同期离职' union
select 3,'开除'
go

CREATE TABLE EmpDismiss
(
    strEmployeeCode VARCHAR(10) PRIMARY KEY,--员工工号
    strEmployeeName VARCHAR(40) NOT NULL,--姓名
    strDepartment VARCHAR(20) NOT NULL,--部门
    strType VARCHAR(40) NOT NULL,--离职类别
    iDay INT NOT NULL --在职天数
)
go
insert into EmpDismiss(strEmployeeCode,strEmployeeName,strDepartment,strType,iDay)
select '001','张三','信息中心','试用期离职',1 union
select '002','李四','信息中心','试用期离职',3 union
select '003','王五','信息中心','试用期离职',35 union
select '004','唯一','信息中心','合同期离职',456 UNION
select '005','小红','行政部','合同期离职',2 UNION
select '006','小小','行政部','开除',222 
GO

按部门和离职类别统计员工离职人数，离职类别增加“（三天内）离职”统计，3天后的分类统计，3天内全部放在第一栏
查询想得到的结果如图

[还在加载中灬]

--动态
DECLARE @SQL VARCHAR(8000)--SQL2005+可以用VARCHAR(MAX)代替
SET @SQL='SELECT strDepartment,SUM(CASE WHEN iDay<=3 THEN 1 ELSE 0 END)AS[（三天内）离职]'
SELECT @SQL=@SQL+',SUM(CASE WHEN iDay>3 AND strType='''+strType+''' THEN 1 ELSE 0 END)['+strType+']'
FROM EmpDismissType GROUP BY strType,ID ORDER BY ID
SET @SQL=@SQL+'FROM EmpDismiss GROUP BY strDepartment'
--PRINT @SQL
EXEC(@SQL)
--静态
SELECT strDepartment
	,SUM(CASE WHEN iDay<=3 THEN 1 ELSE 0 END)AS[（三天内）离职]
	,SUM(CASE WHEN iDay>3 AND strType='试用期离职' THEN 1 ELSE 0 END)[试用期离职]
	,SUM(CASE WHEN iDay>3 AND strType='合同期离职' THEN 1 ELSE 0 END)[合同期离职]
	,SUM(CASE WHEN iDay>3 AND strType='开除' THEN 1 ELSE 0 END)[开除]
FROM EmpDismiss
GROUP BY strDepartment

[zhll2046]

;WITH Cte AS
(
SELECT strDepartment,strType,1 num FROM EmpDismiss
UNION ALL
SELECT strDepartment,N'三天内离职',1 num FROM EmpDismiss 
WHERE iDay<4
)
SELECT strDepartment, ISNULL([三天内离职],0) [三天内离职],ISNULL([试用期离职],0) [试用期离职],
ISNULL([合同期离职],0) [合同期离职],ISNULL([开除],0) [开除] FROM Cte
PIVOT
(
SUM(num) FOR strType IN ([三天内离职],[试用期离职],[合同期离职],[开除])
)PVT

[si12si12]

楼1 的写法 我比较易懂些 楼2 用到行转列 总之 非常感谢两位了