mysql时间段交集的问题

[m0_37535683]

下图是我要的效果，上图是数据的记录，sql语句该怎样实现

[二月十六]

什么规则呢？8点到12点，然后12点到18点，然后第二天？

[zhouyuehai1978]

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T(id INT ,userid INT, start_time TIME,end_time TIME)
Insert #T
SELECT 1,1,'8:00','9:00' UNION ALL
SELECT 2,1,'8:30','10:10' UNION ALL
SELECT 3,1,'10:00','11:00' UNION ALL
SELECT 4,1,'12:00','14:00' UNION ALL
SELECT 5,1,'13:00','15:00' UNION ALL
SELECT 6,1,'8:30','8:45' UNION ALL
SELECT 7,1,'10:00','10:30' UNION ALL
SELECT 8,2,'8:30','10:30' UNION ALL
SELECT 9,2,'8:00','11:00' UNION ALL
SELECT 10,2,'9:00','11:30' 
Go
--测试数据结束
--SELECT * FROM #T AS t
;WITH a AS(
SELECT *,
       ROW_NUMBER() OVER(PARTITION BY t.userid ORDER BY t.start_time) AS nt
FROM   #T AS t)
,b as (                                                   
SELECT userid,
       start_time,
       nt,
       ROW_NUMBER() OVER(ORDER BY a.userid, a.start_time) AS nt1
FROM   a AS a
WHERE  nt = 1
       OR  a.start_time > (
               SELECT MAX(end_time)
               FROM   a AS a1
               WHERE  a.userid = a1.userid
                      AND a.nt > a1.nt
           )
)
SELECT userid,
       start_time,
       CASE 
            WHEN EXISTS (
                     SELECT 1
                     FROM   b AS b1
                     WHERE  b.userid = b1.userid
                            AND b1.nt1 = b.nt1 + 1
                 ) THEN (
                     SELECT MAX(a.end_time)
                     FROM   a AS a
                     WHERE  a.userid = b.userid
                            AND a.nt < (
                                    SELECT b1.nt
                                    FROM   b AS b1
                                    WHERE  b.userid = b1.userid
                                           AND b1.nt1 = b.nt1 + 1
                                )
                 )
            ELSE (
                     SELECT MAX(a.end_time)
                     FROM   a AS a
                     WHERE  a.userid = b.userid
                 )
       END AS end_time
FROM   b AS b

[zhouyuehai1978]

忘记放图了

[m0_37535683]

时间24小时，有区别日期的

[m0_37535683]

mysql数据库的