月考勤报表的TSQL查询

为什么80%的码农都做不了架构师？>>>   

最近考勤模块中需要添加如下图的报表：

 

我们原先的数据库是设计经过各种简化后如下图所示（不考虑批准天数、销假和请假类型等情况）：

下面的SQL语句可以创建如上图的数据表，并填充一下测试数据：

1 -- 用户请假表 2 IF OBJECT_ID ( ' LeaveDays ' ) IS NOT NULL 3 DROP TABLE LeaveDays 4 GO 5 CREATE TABLE LeaveDays ( 6 UserCode varchar ( 8 ) NOT NULL , -- 用户编号 7 BeginDate datetime NOT NULL , -- 请假开始时期 8 Days int DEFAULT 1 NOT NULL -- 请假天数 9 ) 10 GO 11 12 -- 添加测试数据 13 INSERT INTO LeaveDays (UserCode, BeginDate, Days) VALUES ( ' A ' , ' 2013-08-05 ' , 2 ) 14 INSERT INTO LeaveDays (UserCode, BeginDate, Days) VALUES ( ' A ' , ' 2013-08-13 ' , 1 ) 15 INSERT INTO LeaveDays (UserCode, BeginDate, Days) VALUES ( ' A ' , ' 2013-08-26 ' , 3 ) 16 17 INSERT INTO LeaveDays (UserCode, BeginDate, Days) VALUES ( ' B ' , ' 2013-08-01 ' , 1 ) 18 INSERT INTO LeaveDays (UserCode, BeginDate, Days) VALUES ( ' B ' , ' 2013-08-7 ' , 3 ) 19 20 INSERT INTO LeaveDays (UserCode, BeginDate, Days) VALUES ( ' C ' , ' 2013-08-10 ' , 4 ) 21 INSERT INTO LeaveDays (UserCode, BeginDate, Days) VALUES ( ' C ' , ' 2013-08-18 ' , 3 ) 22 INSERT INTO LeaveDays (UserCode, BeginDate, Days) VALUES ( ' C ' , ' 2013-08-24 ' , 3 ) 23 GO

现在数据库中的数据如下图所示：

上图对与我们最终要构造的报表帮助不大，我们首先要查询出来用户在指定日期是否请假，而不是请假的开始日期和请假天数，如下图所示：

要构造上图的查询需要用到数字辅助表（即自增的一个序列），关于数字辅助表的概念可阅读经典书籍《SQL 2005技术内幕：T-SQL查询》，生成数字辅助表的TSQL语句如下：

1 -- 数字辅助表 2 IF OBJECT_ID ( ' dbo.Nums ' ) IS NOT NULL 3 DROP TABLE dbo.Nums; 4 GO 5 CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY ); 6 DECLARE @max AS INT , @rc AS INT ; 7 SET @max = 10000 ; 8 SET @rc = 1 ; 9 10 INSERT INTO Nums VALUES ( 1 ); 11 WHILE @rc * 2 <= @max 12 BEGIN 13 INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums; 14 SET @rc = @rc * 2 ; 15 END 16 17 INSERT INTO dbo.Nums 18 SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max ; 19 GO

然后利用数字辅助表可进行上图的查询：

1 WITH cte_LeaveDays AS 2 ( 3 SELECT BeginDate, BeginDate + Days - 1 AS EndDate,UserCode 4 FROM LeaveDays 5 WHERE BeginDate >= ' 2013-08-01 ' AND BeginDate < ' 2013-09-01 ' 6 ) 7 SELECT BeginDate + n - 1 AS LeaveDay, UserCode FROM cte_LeaveDays 8 JOIN Nums ON BeginDate + n - 1 <= EndDate 9 WHERE n <= 31 10 ORDER BY UserCode, LeaveDay 11

最后需要使用PIVOT关键字进行转列，即可生成最终报表：

1 WITH cte_LeaveDays AS 2 ( 3 SELECT BeginDate, BeginDate + Days - 1 AS EndDate,UserCode 4 FROM LeaveDays 5 WHERE BeginDate >= ' 2013-08-01 ' AND BeginDate < ' 2013-09-01 ' 6 ) 7 SELECT * FROM 8 ( 9 SELECT DAY (BeginDate + n - 1 ) AS Day , UserCode FROM cte_LeaveDays 10 JOIN Nums ON BeginDate + n - 1 <= EndDate 11 WHERE n <= 31 12 ) AS T 13 PIVOT 14 ( 15 COUNT ( Day ) FOR day IN ( 16 [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] , [ 5 ] , [ 6 ] , [ 7 ] , [ 8 ] , [ 9 ] , [ 10 ] , 17 [ 11 ] , [ 12 ] , [ 13 ] , [ 14 ] , [ 15 ] , [ 16 ] , [ 17 ] , [ 18 ] , [ 19 ] , [ 20 ] , 18 [ 21 ] , [ 22 ] , [ 23 ] , [ 24 ] , [ 25 ] , [ 26 ] , [ 27 ] , [ 28 ] , [ 29 ] , [ 30 ] , [ 31 ] ) 19 ) AS PVT 20

完整代码下载

转载于:https://my.oschina.net/bery/blog/160144