MySQL中UNION无法正确合并行：儿童身高体重月度统计问题

我有一张患者表（kid），以及两张记录儿童常规体检身高和体重的表（track_height、track_weight），需求如下：

• 同一月份可能存在多次同类型（身高/体重）测量记录；
• 部分月份可能无任何测量记录；
• 仅展示同时存在身高和体重测量记录的月份；
• 若同一月份存在多次同类型测量，需取平均值，确保每月每种测量仅一条结果。

当前使用的查询语句如下：

(SELECT CONCAT(YEAR(track_height.date_track), '-', LPAD(MONTH(track_height.date_track), 2, 0)) AS date_track, ROUND(AVG(track_height.height), 1) AS height, NULL AS weight
FROM track_height
WHERE track_height.id_kid = 17
GROUP BY YEAR(track_height.date_track), MONTH(track_height.date_track)
ORDER BY track_height.date_track)

UNION

(SELECT CONCAT(YEAR(track_weight.date_track), '-', LPAD(MONTH(track_weight.date_track), 2, 0)) AS date_track, NULL AS height, ROUND(AVG(track_weight.weight), 1) AS weight
FROM track_weight
WHERE track_weight.id_kid = 17
GROUP BY YEAR(track_weight.date_track), MONTH(track_weight.date_track)
ORDER BY track_weight.date_track)

ORDER BY date_track

但查询结果存在两个问题：

1. UNION未按日期合并结果，每个YYYY-MM日期应对应一行，同时包含当月身高和体重的平均值；
2. 2023-02月因缺少体重数据，不应出现在结果中。

表结构及测试数据如下：

CREATE TABLE kid (
  id_kid int(10) UNSIGNED NOT NULL,
  date_insert timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  alias varchar(30) NOT NULL,
  birthday date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO kid (id_kid, date_insert, alias, birthday) VALUES (17, '2023-10-22 17:19:08', 'Jenny', '2020-09-30');

CREATE TABLE track_height (
  id_track int(10) UNSIGNED NOT NULL,
  id_kid int(10) UNSIGNED NOT NULL,
  date_track date NOT NULL,
  height float UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO track_height (id_track, id_kid, date_track, height) VALUES (45, 17, '2023-04-07', 50), (46, 17, '2023-04-30', 52), (101, 17, '2023-01-31', 25), (102, 17, '2023-02-28', 34), (103, 17, '2023-03-31', 48), (104, 17, '2023-03-15', 42), (105, 17, '2023-03-01', 40), (106, 17, '2023-05-17', 55);

CREATE TABLE track_weight (
  id_track int(10) UNSIGNED NOT NULL,
  id_kid int(10) UNSIGNED NOT NULL,
  date_track date NOT NULL,
  weight float UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO track_weight (id_track, id_kid, date_track, weight) VALUES (34, 17, '2023-01-09', 9.6), (35, 17, '2023-01-18', 9.9), (36, 17, '2023-01-31', 10.1), (39, 17, '2023-03-07', 10.5), (40, 17, '2023-03-16', 10.6), (41, 17, '2023-04-05', 10.7), (42, 17, '2023-05-04', 11), (43, 17, '2023-05-30', 11.5);

要解决这两个问题，需先分别计算身高和体重的月平均值，再通过内连接（INNER JOIN）按月份合并结果，自动过滤仅单类数据的月份，同时保证每月一行数据。

修改后的查询语句如下：

SELECT 
    h.date_track,
    h.height,
    w.weight
FROM
    (SELECT 
         CONCAT(YEAR(date_track), '-', LPAD(MONTH(date_track), 2, 0)) AS date_track,
         ROUND(AVG(height), 1) AS height
     FROM track_height
     WHERE id_kid = 17
     GROUP BY YEAR(date_track), MONTH(date_track)) h
INNER JOIN
    (SELECT 
         CONCAT(YEAR(date_track), '-', LPAD(MONTH(date_track), 2, 0)) AS date_track,
         ROUND(AVG(weight), 1) AS weight
     FROM track_weight
     WHERE id_kid = 17
     GROUP BY YEAR(date_track), MONTH(date_track)) w ON h.date_track = w.date_track
ORDER BY h.date_track;

逻辑说明 #

1. 子查询h：计算每个月份的身高平均值，生成date_track（YYYY-MM格式）和height字段；
2. 子查询w：计算每个月份的体重平均值，生成date_track和weight字段；
3. 内连接INNER JOIN：仅保留两个子查询中都存在的date_track，自动排除只有身高或只有体重的月份；
4. 最终结果按date_track排序，每个月份一行，同时包含身高和体重的平均值。

测试结果 #

执行上述语句后，得到的结果如下：

可以看到：

• 每个月份仅一行数据，同时包含身高和体重平均值；
• 2023-02月因缺少体重数据，未出现在结果中。

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