如何生成时段并填充SQL表？如何创建含日期、小时字段的十年时段表？

我来帮你一步步搞定这两个SQL问题，不同数据库的实现细节会有点不一样，下面我会覆盖主流的MySQL、PostgreSQL和SQL Server三种情况：

1. 生成时段并填充至SQL表 #

这里默认你需要的是小时级的时间时段（如果是分钟/天等其他粒度，只需调整逻辑即可），核心思路是先生成目标日期范围，再生成0-23的小时序列，最后通过交叉连接得到所有时段组合，再插入到目标表中。

MySQL 8.0+（支持CTE） #

-- 用递归CTE生成日期范围和小时序列，再插入到目标表
WITH date_range AS (
  SELECT '2010-01-01' AS target_date
  UNION ALL
  SELECT DATE_ADD(target_date, INTERVAL 1 DAY) 
  FROM date_range 
  WHERE target_date < '2020-01-01'
), hour_range AS (
  SELECT 0 AS hour
  UNION ALL
  SELECT hour + 1 
  FROM hour_range 
  WHERE hour < 23
)
INSERT INTO your_target_table (date_column, hour_column)
SELECT dr.target_date, hr.hour
FROM date_range dr
CROSS JOIN hour_range hr;

PostgreSQL #

PostgreSQL自带的generate_series函数能大幅简化操作：

-- 直接生成日期+小时的组合，插入到目标表
INSERT INTO your_target_table (date_column, hour_column)
SELECT 
  generate_series('2010-01-01'::date, '2020-01-01'::date, '1 day') AS target_date,
  generate_series(0, 23) AS hour
ORDER BY target_date, hour;

SQL Server #

-- 递归生成日期和小时序列，插入到目标表
WITH date_range AS (
  SELECT CAST('2010-01-01' AS DATE) AS target_date
  UNION ALL
  SELECT DATEADD(DAY, 1, target_date) 
  FROM date_range 
  WHERE target_date < '2020-01-01'
), hour_range AS (
  SELECT 0 AS hour
  UNION ALL
  SELECT hour + 1 
  FROM hour_range 
  WHERE hour < 23
)
INSERT INTO your_target_table (date_column, hour_column)
SELECT dr.target_date, hr.hour
FROM date_range dr
CROSS JOIN hour_range hr
OPTION (MAXRECURSION 0); -- 递归次数超过默认上限，必须添加此选项

2. 创建包含date、hour独立字段的十年时段表（基于已有日历表） #

你已经有了只包含日期维度的日历表，只需要给每个日期交叉连接0-23的小时序列，就能得到每天的24个时段记录。假设你的日历表叫calendar，日期字段是date，具体操作如下：

MySQL 8.0+ #

-- 创建新表并插入数据
WITH hour_range AS (
  SELECT 0 AS hour
  UNION ALL
  SELECT hour + 1 FROM hour_range WHERE hour < 23
)
SELECT c.date, hr.hour
INTO new_hourly_calendar
FROM calendar c
CROSS JOIN hour_range hr
WHERE c.date BETWEEN '2010-01-01' AND '2020-01-01';

PostgreSQL #

-- 创建新表并插入数据
SELECT c.date, h.hour
INTO new_hourly_calendar
FROM calendar c
CROSS JOIN generate_series(0, 23) AS h(hour)
WHERE c.date BETWEEN '2010-01-01' AND '2020-01-01';

SQL Server #

-- 创建新表并插入数据
WITH hour_range AS (
  SELECT 0 AS hour
  UNION ALL
  SELECT hour + 1 FROM hour_range WHERE hour < 23
)
SELECT c.date, hr.hour
INTO new_hourly_calendar
FROM calendar c
CROSS JOIN hour_range hr
WHERE c.date BETWEEN '2010-01-01' AND '2020-01-01'
OPTION (MAXRECURSION 0);

如果你的数据库不支持CTE（比如MySQL 5.x），可以先手动创建一个小时表：

-- 先创建小时表并插入数据
CREATE TABLE hours (hour INT);
INSERT INTO hours VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);

-- 交叉连接生成新表
SELECT c.date, h.hour
INTO new_hourly_calendar
FROM calendar c
CROSS JOIN hours h
WHERE c.date BETWEEN '2010-01-01' AND '2020-01-01';

内容的提问来源于stack exchange，提问作者Sriharsha