MySQL优化：用子查询替代连接简化学校支持时长统计查询

问题分析 #

你当前的方案需要为每个支持类型单独创建视图，再通过多表LEFT JOIN计算总时长，不仅重复劳动多，维护成本高，新增支持类型时还要修改多处代码。下面提供两种简化方案，重点满足你用子查询替代连接的需求，同时给出更具扩展性的优化方案。

方案一：用关联子查询替代视图与多表连接 #

无需创建任何视图，直接在SELECT子句中通过关联子查询计算每个支持类型的总时长，同时处理NULL值避免总和异常：

SELECT 
  sr.school_id,
  -- 每个支持类型的总时长通过关联子查询计算
  COALESCE((SELECT SUM(sr_inner.smt * st.support_days) 
            FROM support_record sr_inner
            CROSS JOIN support_type st 
            WHERE st.support_id = 'smt' AND sr_inner.school_id = sr.school_id), 0) AS smt_support_total,
  COALESCE((SELECT SUM(sr_inner.acs * st.support_days) 
            FROM support_record sr_inner
            CROSS JOIN support_type st 
            WHERE st.support_id = 'acs' AND sr_inner.school_id = sr.school_id), 0) AS acs_support_total,
  COALESCE((SELECT SUM(sr_inner.hds * st.support_days) 
            FROM support_record sr_inner
            CROSS JOIN support_type st 
            WHERE st.support_id = 'hds' AND sr_inner.school_id = sr.school_id), 0) AS hds_support_total,
  COALESCE((SELECT SUM(sr_inner.rau * st.support_days) 
            FROM support_record sr_inner
            CROSS JOIN support_type st 
            WHERE st.support_id = 'rau' AND sr_inner.school_id = sr.school_id), 0) AS rau_support_total,
  COALESCE((SELECT SUM(sr_inner.wau * st.support_days) 
            FROM support_record sr_inner
            CROSS JOIN support_type st 
            WHERE st.support_id = 'wau' AND sr_inner.school_id = sr.school_id), 0) AS wau_support_total,
  COALESCE((SELECT SUM(sr_inner.fds * st.support_days) 
            FROM support_record sr_inner
            CROSS JOIN support_type st 
            WHERE st.support_id = 'fds' AND sr_inner.school_id = sr.school_id), 0) AS fds_support_total,
  -- 总时长直接累加各子查询结果
  COALESCE((SELECT SUM(sr_inner.smt * st.support_days) FROM support_record sr_inner CROSS JOIN support_type st WHERE st.support_id = 'smt' AND sr_inner.school_id = sr.school_id), 0) +
  COALESCE((SELECT SUM(sr_inner.acs * st.support_days) FROM support_record sr_inner CROSS JOIN support_type st WHERE st.support_id = 'acs' AND sr_inner.school_id = sr.school_id), 0) +
  COALESCE((SELECT SUM(sr_inner.hds * st.support_days) FROM support_record sr_inner CROSS JOIN support_type st WHERE st.support_id = 'hds' AND sr_inner.school_id = sr.school_id), 0) +
  COALESCE((SELECT SUM(sr_inner.rau * st.support_days) FROM support_record sr_inner CROSS JOIN support_type st WHERE st.support_id = 'rau' AND sr_inner.school_id = sr.school_id), 0) +
  COALESCE((SELECT SUM(sr_inner.wau * st.support_days) FROM support_record sr_inner CROSS JOIN support_type st WHERE st.support_id = 'wau' AND sr_inner.school_id = sr.school_id), 0) +
  COALESCE((SELECT SUM(sr_inner.fds * st.support_days) FROM support_record sr_inner CROSS JOIN support_type st WHERE st.support_id = 'fds' AND sr_inner.school_id = sr.school_id), 0) AS support_days_total
FROM support_record sr
GROUP BY sr.school_id;

优点：

• 无需创建和维护多个视图，代码集中在一个查询中
• COALESCE函数处理无数据的情况，避免总和返回NULL

方案二：宽表转窄表+条件聚合（更具扩展性） #

你的support_record是宽表结构（每个支持类型对应一列），先将其转换为窄表，再与support_type关联计算，最后用条件聚合转回宽表格式。这种方案新增支持类型时只需修改少量代码：

WITH unpivoted_support AS (
  -- 将宽表转换为窄表：每个学校-支持类型组合为一行
  SELECT school_id, 'smt' AS support_id, smt AS count FROM support_record
  UNION ALL
  SELECT school_id, 'acs' AS support_id, acs AS count FROM support_record
  UNION ALL
  SELECT school_id, 'hds' AS support_id, hds AS count FROM support_record
  UNION ALL
  SELECT school_id, 'rau' AS support_id, rau AS count FROM support_record
  UNION ALL
  SELECT school_id, 'wau' AS support_id, wau AS count FROM support_record
  UNION ALL
  SELECT school_id, 'fds' AS support_id, fds AS count FROM support_record
),
school_support_totals AS (
  -- 计算每个学校每种支持类型的总时长
  SELECT 
    us.school_id,
    us.support_id,
    SUM(us.count * st.support_days) AS support_total
  FROM unpivoted_support us
  JOIN support_type st ON us.support_id = st.support_id
  GROUP BY us.school_id, us.support_id
)
-- 条件聚合转回宽表，同时计算总时长
SELECT 
  school_id,
  MAX(CASE WHEN support_id = 'smt' THEN support_total END) AS smt_support_total,
  MAX(CASE WHEN support_id = 'acs' THEN support_total END) AS acs_support_total,
  MAX(CASE WHEN support_id = 'hds' THEN support_total END) AS hds_support_total,
  MAX(CASE WHEN support_id = 'rau' THEN support_total END) AS rau_support_total,
  MAX(CASE WHEN support_id = 'wau' THEN support_total END) AS wau_support_total,
  MAX(CASE WHEN support_id = 'fds' THEN support_total END) AS fds_support_total,
  SUM(support_total) AS support_days_total
FROM school_support_totals
GROUP BY school_id;

优点：

• 扩展性强：新增支持类型时，只需在unpivoted_support的UNION ALL中添加一行，再在最后SELECT的CASE WHEN中对应新增一条即可
• 避免重复子查询，数据量大时效率更高
• 逻辑清晰，减少出错概率

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