我有一个患者表(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);
型
谢谢你的帮助!
1条答案
按热度按时间fzwojiic1#
我会这么做
字符串
内联接将确保只考虑两个表中存在的月份。
你有什么理由使用“联盟”吗?