我有一个患者表（kid）和2个表，用于在医疗常规控制中跟踪他们的身高和体重（track_height，track_weight）。

• 在给定的月份，每种类型（身高和体重）可能有多于1次测量。
• 有些月份可能根本没有测量。
• 只有同时测量（身高和体重）的月份才应显示在结果中。
• 如果在任何给定的月份有任何类型的多个测量，则需要对它们进行平均，以便最多每个测量/每个月只有一个结果。

这是我当前的查询：

(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

字符串
问题是，它是这样呈现的：
x1c 0d1x的数据
它应该这样呈现：

的
如您所见，有两个具体问题需要解决：

• UNION语句未按日期组合结果。只能有一个YYYY-MM行同时具有该月的身高和体重平均值。
• 在这种情况下，**February（2023-02）**不应打印，因为我们缺少该月的体重跟踪数据。

我已经设置了一个sqlfiddle示例，但如果您想查看该模式，请参见：

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, id_user, alias, gender, birthday, avatar) 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);

型
谢谢你的帮助！