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

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

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

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

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

要构造上图的查询需要用到数字辅助表（即自增的一个序列），关于数字辅助表的概念可阅读经典书籍《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

完整代码下载