another way of doing, if you really want to do it in SQL
-select '(''' + ID + ''', ''' + convert(varchar(8), START_TIME) + ''', ''' + convert(varchar(8), END_TIME) + '''),' FROM T_KL_TEST3
/*
CREATE TABLE dbo.T_KL_TEST3 (ID VARCHAR(10), START_TIME TIME, END_TIME TIME)
INSERT T_KL_TEST3 VALUES
('A', '08:00:00', '08:20:00'),
('B', '08:15:00', '08:23:00'),
('C', '08:21:00', '08:26:00'),
('D', '08:30:00', '08:32:00'),
('E', '08:33:00', '08:52:00'),
('F', '08:39:00', '08:42:00'),
('G', '08:45:00', '08:49:00')
*/
WITH CTE1 AS (
SELECT * FROM T_KL_TEST3 WHERE ID NOT IN (
select B.ID from T_KL_TEST3 A
LEFT JOIN T_KL_TEST3 B
ON A.START_TIME < B.START_TIME AND A.END_TIME > B.END_TIME
WHERE B.ID IS NOT NULL
)),
CTE AS (
SELECT START_TIME, END_TIME FROM CTE1
UNION ALL
SELECT CTE.START_TIME, T.END_TIME FROM CTE1 T
JOIN CTE
ON T.START_TIME BETWEEN CTE.START_TIME AND CTE.END_TIME
AND T.END_TIME > CTE.END_TIME
)
SELECT ROW_NUMBER() OVER (ORDER BY MIN(START_TIME)) RN,
MIN(START_TIME) as START_TIME, END_TIME
FROM (
SELECT START_TIME, MAX(END_TIME) as END_TIME
FROM CTE
GROUP BY START_TIME
) A
GROUP BY END_TIME
ORDER BY START_TIME, END_TIME
※ 引述《howard7821 (howard7821)》之铭言：
: 标题可能不是很清楚我想表达的
: 目前有个需求是想要计算业务的总通话时长
: 但一个业务同时间可能会与很多客户对话
: 举例来说
: 客户 开始对话时间 结束对话时间
: A 08:00:00 08:20:00
: B 08:15:00 08:23:00
: C 08:21:00 08:26:00
: D 08:30:00 08:32:00
: E 08:33:00 08:52:00
: F 08:39:00 08:42:00
: G 08:45:00 08:49:00
: 以上就是这业务与客户的对话时间表
: 然后想整理成以下资讯
: 1 08:00:00 08:26:00
: 2 08:30:00 08:32:00
: 3 08:33:00 08:52:00
: 也就是对话时间有连贯就合成一笔资料
: 求大神帮小弟解答一下 感谢