如何合并数据表补全全量分钟数据并将未记录值置为0而非Null

要解决这个问题，核心思路是先生成0到59分钟的完整序列，再通过左连接关联你的现有表，把缺失的用户数替换为0。如果需要直接更新原表，还可以批量插入这些缺失的记录。

方法1：生成完整结果集（不修改原表） #

如果只是需要查询出包含所有分钟的完整数据，不需要改动原表，可以用递归CTE（Common Table Expression）生成所有分钟，再左连接原表：

WITH all_minutes AS (
  -- 生成0到59的所有分钟
  SELECT 0 AS minute
  UNION ALL
  SELECT minute + 1 FROM all_minutes WHERE minute < 59
)
SELECT
  am.minute,
  -- 把原表中不存在的分钟的users值从NULL转为0
  COALESCE(t1.users, 0) AS users
FROM all_minutes am
LEFT JOIN table1 t1 ON am.minute = t1.minute
ORDER BY am.minute;

这段代码会返回0到59每个分钟的用户数，原表没记录的分钟自动填充为0。

方法2：直接更新原表（插入缺失的分钟记录） #

如果需要让原表本身包含所有分钟的记录，可以用同样的CTE找出缺失的分钟，然后批量插入：

WITH all_minutes AS (
  SELECT 0 AS minute
  UNION ALL
  SELECT minute + 1 FROM all_minutes WHERE minute < 59
)
INSERT INTO table1 (minute, users)
-- 筛选出原表中不存在的分钟，插入users=0的记录
SELECT am.minute, 0
FROM all_minutes am
LEFT JOIN table1 t1 ON am.minute = t1.minute
WHERE t1.minute IS NULL;

执行完这段SQL后，你的table1就会包含0到59所有分钟的记录，原始未记录的分钟用户数都会被设为0。

不同数据库的兼容说明 #

上面的CTE方法适用于MySQL 8.0+、PostgreSQL、SQL Server等支持递归CTE的数据库。如果是老版本MySQL或者Oracle，生成完整分钟序列的方式略有不同：

• Oracle：可以用CONNECT BY生成序列

  SELECT LEVEL - 1 AS minute FROM DUAL CONNECT BY LEVEL <= 60;
  

• MySQL 5.x及以下：可以用手动拼接的数字表生成0-59的分钟序列，比如：

  SELECT a.minute
  FROM (
    SELECT 0 AS minute UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
    UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
    UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17
    UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23
    UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29
    UNION SELECT 30 UNION SELECT 31 UNION SELECT 32 UNION SELECT 33 UNION SELECT 34 UNION SELECT 35
    UNION SELECT 36 UNION SELECT 37 UNION SELECT 38 UNION SELECT 39 UNION SELECT 40 UNION SELECT 41
    UNION SELECT 42 UNION SELECT 43 UNION SELECT 44 UNION SELECT 45 UNION SELECT 46 UNION SELECT 47
    UNION SELECT 48 UNION SELECT 49 UNION SELECT 50 UNION SELECT 51 UNION SELECT 52 UNION SELECT 53
    UNION SELECT 54 UNION SELECT 55 UNION SELECT 56 UNION SELECT 57 UNION SELECT 58 UNION SELECT 59
  ) a;
  

把这个子查询替换掉之前CTE的部分即可正常使用。

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