这就是我提出的解决方案：

SELECT  (SELECT LEFT(DoW,3) from tbl_businesshrs where tbl_businesshrs.id = 
(SELECT MIN(tbl_businesshrs.id) FROM tbl_businesshrs WHERE 
tbl_businesshrs.tbl_index_id=1 AND tbl_businesshrs.status='Open')) as 
fistDay, 
(SELECT LEFT(DoW,3) from tbl_businesshrs where tbl_businesshrs.id = (SELECT 
MAX(tbl_businesshrs.id) FROM tbl_businesshrs WHERE 
tbl_businesshrs.tbl_index_id=1 AND tbl_businesshrs.status='Open')) as 
lastDay, 
start_time, end_time from tbl_businesshrs WHERE tbl_businesshrs.tbl_index_id 
= 
1 and status = 'Open' GROUP BY status ORDER BY id ASC

以及输出：

一个通用的解决方案有点棘手，但只是为了好玩，这里有一个基于派生的复合“timekey”使用变量来计算块数和行号，然后连接

drop table if exists tbl_businesshrs;
create table tbl_businesshrs(id int auto_increment primary key, 
tbl_index_id int,
dow varchar(3), status varchar(6), start_time time, end_time time);

insert into tbl_businesshrs (dow,tbl_index_id, status,start_time, end_time) values
('mon',1,'closed',null,null),
('tue',1,'open','08:00','17:00'),
('wed',1,'open','08:00','17:00'),
('thu',1,'open','05:00','17:00'),
('fri',1,'open','08:00','16:00'),
('sat',1,'open','08:00','17:00'),
('sun',1,'closed',null,null);

select
        concat('open ', 
        group_concat( 
        case when c.dow = d.dow then concat(c.dow,' ' ,c.start_time,'-',c.end_time) 
        else concat(c.dow,' to ',d.dow, ' ', c.start_time, '-',c.end_time)
        end
        )
        ) open,
        (select group_concat(dow) from tbl_businesshrs t where t.status = 'closed') closed

from
(
select * from
(
select id,dow,tbl_index_id,status,start_time,end_time, concat(start_time,end_time) timekey,
       if (concat(start_time,end_time) <> @timekey, @bn:=@bn+1,@bn:=@bn) bn,
       if (concat(start_time,end_time) <> @timekey, @rn:=1,@rn:=@rn + 1) rn,
         @timekey:=concat(coalesce(start_time,'00:00:00'),coalesce(end_time,'00:00:00')) tk
from tbl_businesshrs t
cross join
(select @bn:=0,@rn:=0,@timekey:= concat(cast('00:00:00' as time),cast('00:00:00' as time))) r
where status = 'open'
order by id
) a 
where rn = 1
) c

join
(
select b.*,
         if(b.bn <> @p ,@rn:=1,@rn:=@rn+1) rn,
         @p:=b.bn p
from
(       
select id,dow,tbl_index_id,status,start_time,end_time, concat(start_time,end_time) timekey,
       if (concat(start_time,end_time) <> @timekey1, @bn1:=@bn1+1,@bn1:=@bn1) bn,
       #if (concat(start_time,end_time) <> @timekey1, @rn1:=1,@rn1:=@rn1+1) rn,
         @timekey1:=concat(coalesce(start_time,'00:00:00'),coalesce(end_time,'00:00:00')) tk

from tbl_businesshrs t
cross join
(select @bn1:=0,

# @rn1:=0,

@timekey1:= concat(cast('00:00:00' as time),cast('00:00:00' as time))) r
where status = 'open'
order by id
) b
cross join(select @p:=0,@rn:=0) r
order by b.bn ,b.id desc
) d
on d.bn  = c.bn 
where d.rn = c.rn
;

+-----------------------------------------------------------------------------------------------------+---------+
| open                                                                                                | closed  |
+-----------------------------------------------------------------------------------------------------+---------+
| open tue to wed 08:00:00-17:00:00,thu 05:00:00-17:00:00,fri 08:00:00-16:00:00,sat 08:00:00-17:00:00 | mon,sun |
+-----------------------------------------------------------------------------------------------------+---------+
1 row in set (0.12 sec)

还有你的样本数据

+-----------------------------------+---------+
| open                              | closed  |
+-----------------------------------+---------+
| open tue to sat 08:00:00-17:00:00 | mon,sun |
+-----------------------------------+---------+
1 row in set (0.00 sec)

注意，我们总是将块号与块号连接起来，行号=1