22,210
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(NAME VARCHAR(10),
START_TIME TIME,
END_TIME TIME)
INSERT INTO #T
SELECT 'A','07:10','12:20' UNION ALL
SELECT 'A','12:30','20:00' UNION ALL
SELECT 'A','21:00','23:00' UNION ALL
SELECT 'B','07:00','20:00'
WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY START_TIME) AS SEQ_1,
COUNT(1) OVER (PARTITION BY NAME) AS QTY
FROM #T)
SELECT *
FROM
(SELECT A.NAME,DATEADD(MINUTE,1,A.END_TIME) AS REST_START,
CASE WHEN A.END_TIME<>'23:59' AND ISNULL(B.NAME,'')='' THEN '23:59' ELSE DATEADD(MINUTE,-1,B.START_TIME) END AS REST_END
FROM CTE A
LEFT JOIN CTE B ON A.NAME=B.NAME AND A.SEQ_1=B.SEQ_1-1
UNION ALL
SELECT NAME,'00:00',DATEADD(MINUTE,-1,START_TIME) FROM CTE WHERE SEQ_1=1 AND START_TIME<>'00:00:00') AS A
ORDER BY NAME,REST_START