MySQL中如何跨两张表多字段关联查找实现区间值匹配？

• 原有SQL关联条件不全：两个CTE关联时仅用ID字段匹配，未关联Description等其他唯一标识字段，如果同一个ID对应多行不同的Description数据，会直接产生笛卡尔积，导致行数指数级膨胀，这是返回结果超1400万行的核心原因
• 写法冗余：拆分两个CTE分别查询from、to的映射值，完全可以在单次查询中通过两次关联主表实现，既减少计算量也避免了关联条件遗漏的风险

不需要拆分多次查询，直接在同一次查询中两次关联映射表（table2）即可，同时可直接代入输入参数做过滤，一步得到符合要求的Cost值：

SELECT 
    t1.Cost
FROM table1 t1
LEFT JOIN table2 g1 ON t1.From_Index_Code = g1.Code
LEFT JOIN table2 g2 ON t1.To_Index_Code = g2.Code
WHERE 
    t1.PARAM1 = 'abc' -- 输入参数PARAM1
    AND t1.PARAM2 = 'xyz' -- 输入参数PARAM2
    AND 150 BETWEEN g1.Value AND g2.Value -- 输入参数Index值，匹配数值区间

如果需要先查看所有区间的映射结果验证数据，去掉区间过滤条件即可，返回行数和table1的过滤后行数完全一致，不会出现行数膨胀的问题：

SELECT 
    t1.ID,
    t1.Description,
    g1.Value AS From_Value,
    g2.Value AS To_Value,
    t1.Cost
FROM table1 t1
LEFT JOIN table2 g1 ON t1.From_Index_Code = g1.Code
LEFT JOIN table2 g2 ON t1.To_Index_Code = g2.Code
WHERE 
    t1.PARAM1 = 'abc'
    AND t1.PARAM2 = 'xyz'

• 给table1的PARAM1、PARAM2字段加联合索引，加快参数过滤速度
• 给table2的Code字段加唯一索引，提升关联效率
• 若确认From_Index_Code和To_Index_Code在table2中一定存在映射值，可将LEFT JOIN改为INNER JOIN，进一步提升查询性能

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