需要帮助找到正确的T-SQL查询

UserId       DateRequested           Approved ApprovedBy  Notes
------------ ----------------------- -------- ----------- -----
1            2011-05-26               0        NULL        NULL
1            2011-05-27               0        NULL        NULL
1            2011-05-28               0        NULL        NULL
1            2011-06-05               0        NULL        NULL
1            2011-06-06               0        NULL        NULL
1            2011-06-25               0        NULL        NULL

这基本上包含员工请求假期的日子.现在,当授予一天或几天时,需要将此数据复制到表单的表中

UserId DateFrom DateTo

所以基本上对于我想要的上述数据：

UserId DateFrom DateTo 
-------------------------------
1      2011-05-26 2011-05-28 
1      2011-06-05 2011-06-06 
1      2011-06-25 2011-06-25

我想在DateFrom和DateTo中连续几天.现在我不知道如何在不使用while循环的情况下执行此操作.这是SQL,所以我更喜欢非迭代解决方案.

请指教！！！

解决方法

;WITH cte AS
(
SELECT *,DATEDIFF(DAY,DateRequested)-
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY DateRequested) AS Grp
FROM YourTable  
WHERE Approved = 1 /*Presumably - but your example data doesn't show this.*/
)
SELECT UserId,MIN(DateRequested) AS DateFrom,MAX(DateRequested) AS DateTo  
FROM cte 
GROUP BY UserId,Grp

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!