34,587
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
userId INT NOT NULL,
addinsDate DATE NOT NULL,
seg INT, --班次时段 0 上午 1 下午 2 晚上
userState INT --0 出勤 1 请假 2 公差 3 迟到 4 早退 5 旷工
)
GO
INSERT INTO t VALUES (1,'2018-08-01',0,0)
INSERT INTO t VALUES (1,'2018-08-01',1,0)
INSERT INTO t VALUES (1,'2018-08-02',0,1)
INSERT INTO t VALUES (1,'2018-08-02',0,2)
INSERT INTO t VALUES (2,'2018-08-02',0,3)
INSERT INTO t VALUES (2,'2018-08-02',0,4)
GO
SELECT
userId
,addinsDate
,SUM(CASE WHEN userState=0 THEN 1 ELSE 0 END) AS [出勤]
,SUM(CASE WHEN userState=1 THEN 1 ELSE 0 END) AS [请假]
,SUM(CASE WHEN userState=2 THEN 1 ELSE 0 END) AS [公差]
,SUM(CASE WHEN userState=3 THEN 1 ELSE 0 END) AS [迟到]
,SUM(CASE WHEN userState=4 THEN 1 ELSE 0 END) AS [早退]
,SUM(CASE WHEN userState=5 THEN 1 ELSE 0 END) AS [旷工]
FROM t
GROUP BY userId,addinsDate