构建多步骤SQL查询：字符替换、表关联与条件统计计数

原SQL的核心错误 #

1. 子查询未指定别名，数据库无法识别后续引用的表/字段
2. RIGHT JOIN的关联对象错误，应该关联Table2而非原Table1
3. 关联条件字段不匹配，需用子查询生成的newmac匹配Table2的MacID
4. 未将关联逻辑与统计逻辑整合，原统计语句未关联Table1的meter_class字段

正确的完整SQL #

SELECT 
    t2.MacID,
    COUNT(CASE WHEN t2.current > t1.meter_class THEN 1 END) AS exceed_count
FROM (
    -- 预处理Table1的MacID，去除冒号并保留所需字段
    SELECT 
        MacID,
        REPLACE(MacID, ":", "") AS newmac,
        meter_form,
        meter_class
    FROM Table1
) t1
RIGHT JOIN Table2 t2
    ON t1.newmac = t2.MacID
GROUP BY t2.MacID
HAVING exceed_count > 10;

逻辑分步说明 #

• 预处理Table1：通过子查询t1，用REPLACE函数把MacID中的冒号替换为空，生成格式匹配Table2的newmac，同时保留需要关联的meter_form和meter_class字段
• RIGHT JOIN关联：以t1.newmac和t2.MacID为关联键，确保Table2的所有行都被保留（即使Table1中无匹配的MacID）
• 条件统计：用CASE WHEN仅统计current > meter_class的有效行（避免右连接产生的NULL值干扰计数）
• 过滤结果：按MacID分组后，通过HAVING只保留计数大于10的结果

补充说明 #

如果需要同时保留meter_form字段，可根据需求调整SELECT和GROUP BY（注意若同一MacID对应多个meter_form，需用聚合函数处理，比如MAX(t1.meter_form)）

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