我有一个更复杂的表，一年中每天的开放时间，人，一些额外的注释等，根据需要分组。
然而，我被一个查询卡住了。
| date | opening_time | day_of_week | blahblah不相关列 | 2023-01-01 | 空 | 7 | … |
|---|---|---|---|
| 2023-01-02 | 12日至16日 | 0 | … |
| 2023-01-03 | 分裂到8 - 16个2 | … | |
| 2023-01-04 | 分裂到8 - 16个3 | … | |
| 2023-01-05 | 分裂到8 - 16个4 | … | |
| 2023-01-06 | 分裂到8 - 16个5 | … | |
| 2023-01-07 | 分裂到8 - 16个6 | … | |
| … | … | … | … |
| … | … | … | … |
| … | … | … | … |
| 2023-01-14 | 空 | 6 | … |
| … | … | … | … |
| … | … | … | … |
| … | … | … | … |
对于开放时间，通常的期望是每周的时间表，并且易于阅读。因此，group by的最佳值为week of year。既然你要找的是From和To，那么除了星期六，其他工作日似乎都是开放的。星期天不营业。但是，如果情况并非如此，请在可行的情况下根据您的需要随意调整查询。
- 在SQLite中，星期天的星期几是0。因此，CTE中的查找表。
- 组开放日期按周划分;也可以从/到
day of week.
with cte_day_of_week as (
select 1 as dow, 'Monday' as day_of_week union all
select 2 as dow, 'Tuesday' as day_of_week union all
select 3 as dow, 'Wednesday' as day_of_week union all
select 4 as dow, 'Thursday' as day_of_week union all
select 5 as dow, 'Friday' as day_of_week union all
select 6 as dow, 'Saturday' as day_of_week union all
select 7 as dow, 'Sunday' as day_of_week),
cte_opening_dates as (
select group_concat(date) as opening_dates,
min(day_of_week) as from_dow,
max(day_of_week) as to_dow,
strftime('%W',date) as week_of_year
from office_hour
where opening_time is not null
group by week_of_year)
select o.opening_dates as weekly_opening_dates,
'From ' || fd.day_of_week ||
' to ' || td.day_of_week as day_of_week_range
from cte_opening_dates o
join cte_day_of_week fd
on o.from_dow = fd.dow
join cte_day_of_week td
on o.to_dow = td.dow
结果是52行每周时间表:
weekly_opening_dates |day_of_week_range |
-----------------------------------------------------------------+-----------------------+
2023-01-02,2023-01-03,2023-01-04,2023-01-05,2023-01-06,2023-01-07|From Monday to Saturday|
2023-01-09,2023-01-10,2023-01-11,2023-01-12,2023-01-13 |From Monday to Friday |
2023-01-16,2023-01-17,2023-01-18,2023-01-19,2023-01-20 |From Monday to Friday |
2023-01-23,2023-01-24,2023-01-25,2023-01-26,2023-01-27 |From Monday to Friday |
2023-01-30,2023-01-31,2023-02-01,2023-02-02,2023-02-03,2023-02-04|From Monday to Saturday|
2023-02-06,2023-02-07,2023-02-08,2023-02-09,2023-02-10 |From Monday to Friday |
2023-02-13,2023-02-14,2023-02-15,2023-02-16,2023-02-17 |From Monday to Friday |
2023-02-20,2023-02-21,2023-02-22,2023-02-23,2023-02-24 |From Monday to Friday |
2023-02-27,2023-02-28,2023-03-01,2023-03-02,2023-03-03 |From Monday to Friday |
2023-03-06,2023-03-07,2023-03-08,2023-03-09,2023-03-10 |From Monday to Friday |
2023-03-13,2023-03-14,2023-03-15,2023-03-16,2023-03-17,2023-03-18|From Monday to Saturday|
2023-03-20,2023-03-21,2023-03-22,2023-03-23,2023-03-24,2023-03-25|From Monday to Saturday|
2023-03-27,2023-03-28,2023-03-29,2023-03-30,2023-03-31 |From Monday to Friday |
2023-04-03,2023-04-04,2023-04-05,2023-04-06,2023-04-07 |From Monday to Friday |
2023-04-10,2023-04-11,2023-04-12,2023-04-13,2023-04-14 |From Monday to Friday |
2023-04-17,2023-04-18,2023-04-19,2023-04-20,2023-04-21 |From Monday to Friday |
2023-04-24,2023-04-25,2023-04-26,2023-04-27,2023-04-28 |From Monday to Friday |
2023-05-01,2023-05-02,2023-05-03,2023-05-04,2023-05-05 |From Monday to Friday |
2023-05-08,2023-05-09,2023-05-10,2023-05-11,2023-05-12,2023-05-13|From Monday to Saturday|
2023-05-15,2023-05-16,2023-05-17,2023-05-18,2023-05-19 |From Monday to Friday |
2023-05-22,2023-05-23,2023-05-24,2023-05-25,2023-05-26,2023-05-27|From Monday to Saturday|
2023-05-29,2023-05-30,2023-05-31,2023-06-01,2023-06-02 |From Monday to Friday |
2023-06-05,2023-06-06,2023-06-07,2023-06-08,2023-06-09,2023-06-10|From Monday to Saturday|
2023-06-12,2023-06-13,2023-06-14,2023-06-15,2023-06-16 |From Monday to Friday |
2023-06-19,2023-06-20,2023-06-21,2023-06-22,2023-06-23 |From Monday to Friday |
2023-06-26,2023-06-27,2023-06-28,2023-06-29,2023-06-30,2023-07-01|From Monday to Saturday|
2023-07-03,2023-07-04,2023-07-05,2023-07-06,2023-07-07,2023-07-08|From Monday to Saturday|
2023-07-10,2023-07-11,2023-07-12,2023-07-13,2023-07-14 |From Monday to Friday |
2023-07-17,2023-07-18,2023-07-19,2023-07-20,2023-07-21 |From Monday to Friday |
2023-07-24,2023-07-25,2023-07-26,2023-07-27,2023-07-28 |From Monday to Friday |
2023-07-31,2023-08-01,2023-08-02,2023-08-03,2023-08-04 |From Monday to Friday |
2023-08-07,2023-08-08,2023-08-09,2023-08-10,2023-08-11 |From Monday to Friday |
2023-08-14,2023-08-15,2023-08-16,2023-08-17,2023-08-18 |From Monday to Friday |
2023-08-21,2023-08-22,2023-08-23,2023-08-24,2023-08-25 |From Monday to Friday |
2023-08-28,2023-08-29,2023-08-30,2023-08-31,2023-09-01,2023-09-02|From Monday to Saturday|
2023-09-04,2023-09-05,2023-09-06,2023-09-07,2023-09-08 |From Monday to Friday |
2023-09-11,2023-09-12,2023-09-13,2023-09-14,2023-09-15,2023-09-16|From Monday to Saturday|
2023-09-18,2023-09-19,2023-09-20,2023-09-21,2023-09-22 |From Monday to Friday |
2023-09-25,2023-09-26,2023-09-27,2023-09-28,2023-09-29 |From Monday to Friday |
2023-10-02,2023-10-03,2023-10-04,2023-10-05,2023-10-06 |From Monday to Friday |
2023-10-09,2023-10-10,2023-10-11,2023-10-12,2023-10-13,2023-10-14|From Monday to Saturday|
2023-10-16,2023-10-17,2023-10-18,2023-10-19,2023-10-20 |From Monday to Friday |
2023-10-23,2023-10-24,2023-10-25,2023-10-26,2023-10-27,2023-10-28|From Monday to Saturday|
2023-10-30,2023-10-31,2023-11-01,2023-11-02,2023-11-03,2023-11-04|From Monday to Saturday|
2023-11-06,2023-11-07,2023-11-08,2023-11-09,2023-11-10 |From Monday to Friday |
2023-11-13,2023-11-14,2023-11-15,2023-11-16,2023-11-17 |From Monday to Friday |
2023-11-20,2023-11-21,2023-11-22,2023-11-23,2023-11-24,2023-11-25|From Monday to Saturday|
2023-11-27,2023-11-28,2023-11-29,2023-11-30,2023-12-01,2023-12-02|From Monday to Saturday|
2023-12-04,2023-12-05,2023-12-06,2023-12-07,2023-12-08,2023-12-09|From Monday to Saturday|
2023-12-11,2023-12-12,2023-12-13,2023-12-14,2023-12-15,2023-12-16|From Monday to Saturday|
2023-12-18,2023-12-19,2023-12-20,2023-12-21,2023-12-22 |From Monday to Friday |
2023-12-25,2023-12-26,2023-12-27,2023-12-28,2023-12-29,2023-12-30|From Monday to Saturday|