MySQL：如何根据输入值匹配height区间查询对应code字段

需求说明 #

需要编写一条MySQL SELECT语句，传入整数或小数类型的输入值，从example_table表中查询对应的code字段值，匹配规则如下：

• 输入值落在某一行的height与下一行的height构成的左闭右开区间时，返回当前行的code
• 输入值大于等于最后一行的height时，返回最后一行的code

示例表结构与数据 #

表结构创建语句：

CREATE TABLE `example_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `height` INT(3) NOT NULL DEFAULT '0',
  `code` DECIMAL(6,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1;

示例数据：

解决方案 #

方案1：使用窗口函数（MySQL 8.0+）

利用LEAD()窗口函数获取每一行的下一行height值，再通过条件筛选匹配区间：

SELECT code
FROM (
    SELECT 
        code,
        height,
        LEAD(height) OVER (ORDER BY height) AS next_height
    FROM example_table
    ORDER BY height
) AS temp
WHERE 
    -- 替换为实际输入值，比如5.99、6.1等
    5.99 >= height 
    AND (next_height IS NULL OR 5.99 < next_height)
LIMIT 1;

逻辑说明：

1. 内层查询通过LEAD(height) OVER (ORDER BY height)，按height升序获取每一行的下一行height，最后一行的next_height为NULL
2. 外层查询筛选满足「输入值≥当前行height」且「下一行height不存在（即最后一行）或输入值<下一行height」的记录
3. LIMIT 1确保只返回唯一匹配的结果

方案2：自连接兼容低版本MySQL

如果你的MySQL版本低于8.0（不支持窗口函数），可通过自连接实现，前提是id与height的递增顺序一致（示例数据符合该条件）：

SELECT e1.code
FROM example_table e1
LEFT JOIN example_table e2 ON e2.id = e1.id + 1
WHERE 
    -- 替换为实际输入值
    6.1 >= e1.height 
    AND (e2.height IS NULL OR 6.1 < e2.height)
ORDER BY e1.height
LIMIT 1;

示例验证 #

• 输入0：返回3.2
• 输入5.99/6：返回5.5
• 输入6.1：返回7.4
• 输入165/165.1：返回40.5

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