MySQL中如何将多条SELECT查询语句合并为一条？

你需要将5条独立查询的结果整合为同一行展示对应月份的所有指标，以下提供两种可行方案：

方案一：子查询LEFT JOIN关联 #

将每个原查询作为子查询，通过月份字段关联，确保所有存在数据的月份都被保留，缺失的指标会显示为NULL：

SELECT
    COALESCE(r.mon, i.mon, q.mon, rt.mon, d.mon) AS mon,
    r.RxInsp,
    i.InspQs,
    q.QsQx,
    rt.QtRp,
    d.RpDl
FROM
    (SELECT month(AssDate) as mon, Round(AVG(DATEDIFF(AssDate,OpenedDate))) as RxInsp FROM tbl_jobcards WHERE year(OpenedDate) = 2023 AND month(AssDate) > 0 GROUP BY mon) r
LEFT JOIN
    (SELECT month(QuoteSent) as mon, Round(AVG(DATEDIFF(QuoteSent, AssDate))) as InspQs FROM tbl_jobcards WHERE year(OpenedDate) = 2023 AND month(QuoteSent) > 0 GROUP BY mon) i ON r.mon = i.mon
LEFT JOIN
    (SELECT month(OrderReceived) as mon, Round(AVG(DATEDIFF(OrderReceived, QuoteSent))) as QsQx FROM tbl_jobcards WHERE year(OpenedDate) = 2023 AND month(OrderReceived) > 0 GROUP BY mon) q ON COALESCE(r.mon, i.mon) = q.mon
LEFT JOIN
    (SELECT month(Repair1Date) as mon, Round(AVG(DATEDIFF(Repair1Date,QuoteSent))) as QtRp FROM tbl_jobcards WHERE year(OpenedDate) = 2023 AND month(Repair1Date) > 0 GROUP BY mon) rt ON COALESCE(r.mon, i.mon, q.mon) = rt.mon
LEFT JOIN
    (SELECT month(DateDelivered) as mon, Round(AVG(DATEDIFF(DateDelivered,Repair1Date))) as RpDl FROM tbl_jobcards WHERE year(OpenedDate) = 2023 AND month(DateDelivered) > 0 GROUP BY mon) d ON COALESCE(r.mon, i.mon, q.mon, rt.mon) = d.mon
ORDER BY mon;

方案二：UNION ALL + 条件聚合 #

先通过UNION ALL将所有查询结果按行合并，再用条件聚合将行转列为同一月份的多列，逻辑更清晰：

SELECT
    mon,
    MAX(CASE WHEN metric = 'RxInsp' THEN value END) AS RxInsp,
    MAX(CASE WHEN metric = 'InspQs' THEN value END) AS InspQs,
    MAX(CASE WHEN metric = 'QsQx' THEN value END) AS QsQx,
    MAX(CASE WHEN metric = 'QtRp' THEN value END) AS QtRp,
    MAX(CASE WHEN metric = 'RpDl' THEN value END) AS RpDl
FROM
    (
        SELECT month(AssDate) as mon, 'RxInsp' as metric, Round(AVG(DATEDIFF(AssDate,OpenedDate))) as value 
        FROM tbl_jobcards 
        WHERE year(OpenedDate) = 2023 AND month(AssDate) > 0 
        GROUP BY mon
        UNION ALL
        SELECT month(QuoteSent) as mon, 'InspQs' as metric, Round(AVG(DATEDIFF(QuoteSent, AssDate))) as value 
        FROM tbl_jobcards 
        WHERE year(OpenedDate) = 2023 AND month(QuoteSent) > 0 
        GROUP BY mon
        UNION ALL
        SELECT month(OrderReceived) as mon, 'QsQx' as metric, Round(AVG(DATEDIFF(OrderReceived, QuoteSent))) as value 
        FROM tbl_jobcards 
        WHERE year(OpenedDate) = 2023 AND month(OrderReceived) > 0 
        GROUP BY mon
        UNION ALL
        SELECT month(Repair1Date) as mon, 'QtRp' as metric, Round(AVG(DATEDIFF(Repair1Date,QuoteSent))) as value 
        FROM tbl_jobcards 
        WHERE year(OpenedDate) = 2023 AND month(Repair1Date) > 0 
        GROUP BY mon
        UNION ALL
        SELECT month(DateDelivered) as mon, 'RpDl' as metric, Round(AVG(DATEDIFF(DateDelivered,Repair1Date))) as value 
        FROM tbl_jobcards 
        WHERE year(OpenedDate) = 2023 AND month(DateDelivered) > 0 
        GROUP BY mon
    ) AS combined
GROUP BY mon
ORDER BY mon;

为什么之前的尝试没成功 #

• UNION是合并行，而你需要的是合并列，直接使用会得到多行数据而非同一行的多指标；
• 若使用JOIN时用了INNER JOIN，会丢失仅存在部分指标的月份；未用COALESCE处理NULL的月份字段，也会导致关联失效；
• 嵌套SELECT若未结合条件聚合或正确的关联逻辑，无法将分散的指标整合到同一行。

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