如何将包含嵌套子查询的MySQL语句改写为等价的Join连接查询

核心逻辑说明 #

SQL的WHERE执行顺序早于SELECT子句，因此不允许直接引用SELECT中定义的计算别名，这也是当时选择嵌套子查询的核心原因——但实际上不需要嵌套派生表，直接将计算表达式写入过滤条件即可，原SQL的多表Join逻辑已经完整，不需要额外新增Join关联。

改写后等价SQL #

SELECT 
  LEFT(FROM_UNIXTIME(c.capture_time/1000), 10) AS date,
  a.name AS system_name,
  b.name AS device_name,
  IF(
    c.valid_object_count = 0,
    '00.00%',
    CONCAT(FORMAT(c.read_count / c.valid_object_count * 100, 2), '%')
  ) AS valid_read_rate
FROM sick_il_services.system_config a
INNER JOIN sick_il_services.system_device b
  ON a.id = b.system_id
INNER JOIN sick_il_aap.daily_device_read_rate c
  ON b.id = c.device_id
WHERE 
  c.valid_object_count > 0 
  AND (c.read_count / c.valid_object_count * 100) < 100
GROUP BY 
  c.capture_time, a.name, b.name
ORDER BY 
  c.capture_time, a.name, b.name;

改动明细 #

• 去掉了冗余的外层派生表嵌套，将原外层过滤逻辑直接落到WHERE子句中，查询执行效率会比嵌套子查询更高
• 增加c.valid_object_count > 0判断：和原逻辑完全等价，原逻辑中valid_object_count=0时计算结果为null，本身就不满足<100的筛选条件，该判断仅为避免数据库抛出除零警告
• 保持了原SQL的分组、排序粒度：如果需要按天维度聚合而非按每次采集时间点统计，只需将GROUP BY、ORDER BY中的c.capture_time替换为和SELECT一致的date表达式即可
• 去掉了无意义的where 1=1条件

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