MySQL无关联多表多列按小时匹配查询实现方案

核心结论：不需要重构数据库结构，不需要额外建立关联表，用标准SQL语法即可实现按hour维度横向对齐的需求。你之前使用UNION ALL未得到预期结果，是写法存在问题，并非UNION ALL或关联语法无法满足场景。

现有数据与需求回顾 #

表结构与样本数据 #

• Table1：字段含id、hour、date、tableValue1、tableValue2，2020-05-29共2条数据：
  • hour=3，tableValue1=123
  • hour=2，tableValue1=1500
• Table2：字段含id、hour、date、tableValue3、tableValue4，2020-05-29共2条数据：
  • hour=1，tableValue3=4545
  • hour=3，tableValue3=5698
• Table3：字段含id、hour、date、tableValue5、tableValue6，2020-05-29共2条数据：
  • hour=2，tableValue5=7841
  • hour=1，tableValue5=1485

预期输出 #

返回hour、tableValue1、tableValue3、tableValue5四列，同hour数据横向合并，无匹配值填0：

原有写法问题 #

你之前写的UNION ALL语句存在两个明显错误：

1. SQL语法顺序错误：FROM子句需要写在WHERE子句之前
2. 列数、列语义不统一：每个子查询只返回2列，没有对另外两个目标字段补0占位，最终结果是纵向堆叠的零散数据，无法做横向对齐

-- 错误写法示例
SELECT hour , tableValue1 WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10 FROM table1 
UNION ALL
SELECT hour , tableValue3 WHERE date = "2020-05-29" AND hour BETWEEN 0 AND 10 FROM table2 
UNION ALL
SELECT hour , tableValue5 WHERE date = "2020-05-29" AND hour BETWEEN 10 AND 10 FROM table3 

可用实现方案 #

方案1：UNION ALL + 分组聚合（全数据库兼容，写法最简单） #

把三个表的查询结果统一成相同列结构，非当前表的目标字段填0，再按hour分组取各字段的非0最大值即可，兼容性最好，写法不易出错：

SELECT
  hour,
  MAX(tableValue1) AS tableValue1,
  MAX(tableValue3) AS tableValue3,
  MAX(tableValue5) AS tableValue5
FROM (
  SELECT hour, tableValue1, 0 AS tableValue3, 0 AS tableValue5 
  FROM Table1 
  WHERE date = '2020-05-29' AND hour BETWEEN 0 AND 10
  UNION ALL
  SELECT hour, 0 AS tableValue1, tableValue3, 0 AS tableValue5 
  FROM Table2 
  WHERE date = '2020-05-29' AND hour BETWEEN 0 AND 10
  UNION ALL
  SELECT hour, 0 AS tableValue1, 0 AS tableValue3, tableValue5 
  FROM Table3 
  WHERE date = '2020-05-29' AND hour BETWEEN 0 AND 10
) AS combined_data
GROUP BY hour
ORDER BY hour;

方案2：外连接关联 #

基于三张表共有的hour字段做关联，用COALESCE/IFNULL把空值填充为0即可。注意MySQL不支持FULL OUTER JOIN，需要用左连接+UNION的方式模拟全外连接，支持全外连接的数据库（PostgreSQL、Oracle等）可以直接写全外连接逻辑。

支持全外连接的数据库写法

SELECT
  COALESCE(t1.hour, t2.hour, t3.hour) AS hour,
  COALESCE(t1.tableValue1, 0) AS tableValue1,
  COALESCE(t2.tableValue3, 0) AS tableValue3,
  COALESCE(t3.tableValue5, 0) AS tableValue5
FROM
  (SELECT hour, tableValue1 FROM Table1 WHERE date = '2020-05-29' AND hour BETWEEN 0 AND 10) t1
  FULL OUTER JOIN
  (SELECT hour, tableValue3 FROM Table2 WHERE date = '2020-05-29' AND hour BETWEEN 0 AND 10) t2
  ON t1.hour = t2.hour
  FULL OUTER JOIN
  (SELECT hour, tableValue5 FROM Table3 WHERE date = '2020-05-29' AND hour BETWEEN 0 AND 10) t3
  ON COALESCE(t1.hour, t2.hour) = t3.hour
ORDER BY hour;

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