MySQL UNION合并不同列后去除空值并上移昨日数据的实现方法

你的现有SQL通过UNION将今日、昨日的小时统计结果分成两行，导致每行存在一个NULL值。要实现同一小时的今日、昨日数据在同一行且去除NULL，可以通过按小时关联两个统计子查询的方式修改：

通用版本（支持FULL OUTER JOIN的数据库，如PostgreSQL、SQL Server等） #

SELECT
  COALESCE(t1.hour_num, t2.hour_num) AS hour_num,
  COALESCE(t1.toDay, 0) AS toDay,
  COALESCE(t2.yesterDay, 0) AS yesterDay
FROM
  -- 统计今日各小时的用户数
  (SELECT
     HOUR(user_datetime) AS hour_num,
     COUNT(1) AS toDay
   FROM bas_user
   WHERE user_datetime >= CURDATE() AND user_datetime <= NOW()
   GROUP BY HOUR(user_datetime)) t1
-- 关联昨日的小时统计
FULL OUTER JOIN
  (SELECT
     HOUR(user_datetime) AS hour_num,
     COUNT(1) AS yesterDay
   FROM bas_user
   WHERE user_datetime >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
     AND user_datetime <= DATE_SUB(NOW(), INTERVAL 1 DAY)
   GROUP BY HOUR(user_datetime)) t2
ON t1.hour_num = t2.hour_num
ORDER BY hour_num;

MySQL兼容版本（MySQL不支持FULL OUTER JOIN） #

SELECT
  t1.hour_num,
  t1.toDay,
  COALESCE(t2.yesterDay, 0) AS yesterDay
FROM
  (SELECT
     HOUR(user_datetime) AS hour_num,
     COUNT(1) AS toDay
   FROM bas_user
   WHERE user_datetime >= CURDATE() AND user_datetime <= NOW()
   GROUP BY HOUR(user_datetime)) t1
LEFT JOIN
  (SELECT
     HOUR(user_datetime) AS hour_num,
     COUNT(1) AS yesterDay
   FROM bas_user
   WHERE user_datetime >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
     AND user_datetime <= DATE_SUB(NOW(), INTERVAL 1 DAY)
   GROUP BY HOUR(user_datetime)) t2
ON t1.hour_num = t2.hour_num

UNION ALL

SELECT
  t2.hour_num,
  COALESCE(t1.toDay, 0) AS toDay,
  t2.yesterDay
FROM
  (SELECT
     HOUR(user_datetime) AS hour_num,
     COUNT(1) AS toDay
   FROM bas_user
   WHERE user_datetime >= CURDATE() AND user_datetime <= NOW()
   GROUP BY HOUR(user_datetime)) t1
RIGHT JOIN
  (SELECT
     HOUR(user_datetime) AS hour_num,
     COUNT(1) AS yesterDay
   FROM bas_user
   WHERE user_datetime >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
     AND user_datetime <= DATE_SUB(NOW(), INTERVAL 1 DAY)
   GROUP BY HOUR(user_datetime)) t2
ON t1.hour_num = t2.hour_num
WHERE t1.hour_num IS NULL

ORDER BY hour_num;

关键说明 #

1. 替换UNION为JOIN：将今日、昨日的统计分别作为子查询，通过hour_num（小时数）关联，实现同一行展示同小时的两日数据。
2. 消除NULL值：用COALESCE将无数据的小时统计值转为0，彻底去除NULL；若不需要转0，可去掉COALESCE保留原始NULL，但结果会更规整。
3. 简化时间条件：直接用日期函数比较，替代UNIX_TIMESTAMP转换，代码更易读维护。

内容的提问来源于stack exchange，提问作者风清扬