34,593
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #T
(
x INT,
l VARCHAR(10)
)
INSERT INTO #T VALUES(1, '人员A')
INSERT INTO #T VALUES(2, '人员A')
INSERT INTO #T VALUES(3, '人员A')
INSERT INTO #T VALUES(4, '人员A')
INSERT INTO #T VALUES(5, '人员B')
INSERT INTO #T VALUES(6, '人员B')
INSERT INTO #T VALUES(7, '人员B')
INSERT INTO #T VALUES(8, '人员A')
INSERT INTO #T VALUES(9, '人员A')
SELECT l ,CONVERT(VARCHAR(10),MIN(x)) +'-'+CONVERT(VARCHAR(10),MAX(x)) AS r FROM
(
SELECT *,x-ROW_NUMBER() OVER(PARTITION BY l ORDER BY x) AS y FROM #T
) a
GROUP BY y,l