sqlserver 多个时间合并成时间段

Question

CREATE TABLE #tmptable(
    rq datetime NOT NULL PRIMARY KEY CLUSTERED,
    sn int IDENTITY(1,1) NOT NULL，
    userId int,
)
GO
INSERT #tmptable VALUES('20170703', 1)
INSERT #tmptable VALUES('20170704', 1)
INSERT #tmptable VALUES('20170705', 1)
INSERT #tmptable VALUES('20170706', 1)
INSERT #tmptable VALUES('20170707', 1)
INSERT #tmptable VALUES('20170708', 1)
INSERT #tmptable VALUES('20170710', 1)
INSERT #tmptable VALUES('20170711', 1)
INSERT #tmptable VALUES('20170712', 1)
INSERT #tmptable VALUES('20170713', 1)
INSERT #tmptable VALUES('20170714', 1)
INSERT #tmptable VALUES('20170715', 1)
INSERT #tmptable VALUES('20170717', 1)
INSERT #tmptable VALUES('20170714', 2)
INSERT #tmptable VALUES('20170715', 2)
INSERT #tmptable VALUES('20170717', 2)
GO

能不能不用循环之类的将它们合并成一条记录：

开始日期      结束日期            USERID
=========== =================  =========
2017-07-03  2017-07-17         1
2017-07-14  2017-07-17         2


要求隔开周六日是可以记为连续日期，但隔开非周六日即为非连续日期不能合并

Answer 1

用循环是高级语言的写法，没啥负责的骂我这里提供一种存储用数据库完成的思路
你看看以下语句，我考虑了比你测试数据更多的情况：
同一个userID 不连续
某个用户只有一条
我这里假设 @@DATEFIRST 是默认的7，也就是每周的第一天按周日开始，如果你的设置不同掉过周末的判断要改下

if object_id('tempdb..#tmptable') is not null drop table #tmptable
go
CREATE TABLE #tmptable(
    rq datetime NOT NULL,
    sn int IDENTITY(1,1) NOT NULL,
    userId int
)
GO
INSERT #tmptable VALUES('20170703', 1)
INSERT #tmptable VALUES('20170704', 1)
INSERT #tmptable VALUES('20170705', 1)
INSERT #tmptable VALUES('20170706', 1)
INSERT #tmptable VALUES('20170707', 1)
INSERT #tmptable VALUES('20170708', 1)
INSERT #tmptable VALUES('20170710', 1)
INSERT #tmptable VALUES('20170711', 1)
--INSERT #tmptable VALUES('20170712', 1)
INSERT #tmptable VALUES('20170713', 1)
INSERT #tmptable VALUES('20170714', 1)
INSERT #tmptable VALUES('20170715', 1)
INSERT #tmptable VALUES('20170717', 1)
INSERT #tmptable VALUES('20170714', 2)
INSERT #tmptable VALUES('20170715', 2)
INSERT #tmptable VALUES('20170717', 2)
INSERT #tmptable VALUES('20170717', 3)
GO
SELECT MIN(t.rq) AS 开始日期 , MAX(t.n_rq) AS 结束日期 ,t.userId,T.grp
FROM (
    SELECT t1.sn, t1.rq,t1.userId,ISNULL(t2.rq,t1.rq) AS n_rq--,t2.userId,ROW_NUMBER()OVER(PARTITION BY t1.userId,t2.userId ORDER BY t1.sn) AS seq
           ,t1.sn-ROW_NUMBER()OVER(PARTITION BY t1.userId,t2.userId ORDER BY t1.sn) AS grp
    FROM #tmptable AS t1 
    LEFT JOIN #tmptable AS t2 ON t1.userId=t2.userId AND DATEDIFF(d,t1.rq,t2.rq)=CASE DATEPART(WEEKDAY,t1.rq) WHEN 6 THEN 3 WHEN 7 THEN 2 ELSE 1 END 

    WHERE t2.rq IS NOT NULL OR
     (t2.rq IS NULL AND 
     NOT EXISTS(SELECT 0 FROM #tmptable AS t WHERE t.userId=t1.userId AND DATEDIFF(d,t.rq,t1.rq)>=CASE DATEPART(WEEKDAY,t.rq) WHEN 6 THEN 3 WHEN 7 THEN 2 ELSE 1 END)  ) 
  --  ORDER BY t1.sn
) AS t GROUP BY t.grp,t.userId

开始日期	结束日期	userId	grp
2017-07-03 00:00:00:000	2017-07-11 00:00:00:000	1	0
2017-07-13 00:00:00:000	2017-07-17 00:00:00:000	1	1
2017-07-14 00:00:00:000	2017-07-17 00:00:00:000	2	12
2017-07-17 00:00:00:000	2017-07-17 00:00:00:000	3	15