如何从hospital、doctor、test三张表查询平均报告生成时间最短的医院

完整实现语句 #

SELECT h.name AS hospital_name
FROM test AS t
INNER JOIN doctor AS d ON t.doctor_id = d.id
INNER JOIN hospital AS h ON d.hospital_name = h.name
GROUP BY h.name
ORDER BY AVG(TIMESTAMPDIFF(minute, TIMESTAMP(test_date, test_time), TIMESTAMP(report_date, report_time))) ASC
LIMIT 1;

逻辑说明 #

• 保留原有三表关联逻辑，关联test、doctor、hospital三张表拿到每条检测记录对应的所属医院
• 用GROUP BY h.name按医院维度分组，对每个医院的所有检测记录做聚合计算
• 用AVG()聚合函数计算每个医院所有检测记录的平均报告生成时长
• 按平均时长升序排序后，用LIMIT 1取排在第一位的记录，就是平均报告生成时间最短的医院

多并列最短场景兼容 #

如果存在多个医院平均时长完全一致、同为最短的情况，需要把所有符合条件的医院都查出来，可以用窗口函数实现：

WITH hospital_avg AS (
    SELECT 
        h.name AS hospital_name,
        AVG(TIMESTAMPDIFF(minute, TIMESTAMP(test_date, test_time), TIMESTAMP(report_date, report_time))) AS avg_duration
    FROM test AS t
    INNER JOIN doctor AS d ON t.doctor_id = d.id
    INNER JOIN hospital AS h ON d.hospital_name = h.name
    GROUP BY h.name
)
SELECT hospital_name
FROM hospital_avg
WHERE avg_duration = (SELECT MIN(avg_duration) FROM hospital_avg);

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