如何优雅实现SQL查询：为整数列表元素找最大小于它的小数

需求描述 #

给定存储小数的数据表，以及一个整数列表，编写SQL查询返回列表中每个整数对应的、数据表中小于该整数的最大小数。

现有数据表 #

整数列表 #

integer_list = [2,50,2000]

预期结果 #

你的尝试代码 #

WITH get_2    AS (SELECT MAX(decimal) FROM table WHERE decimal < 2  ),
     get_50   AS (SELECT MAX(decimal) FROM table WHERE decimal < 50 ),
     get_2000 AS (SELECT MAX(decimal) FROM table WHERE decimal < 2000),
SELECT * FROM get_2
UNION ALL 
SELECT * FROM get_50
UNION ALL 
SELECT * FROM get_2000

更优雅的实现方式 #

可以通过构建整数列表的临时数据集，再关联原表分组取最大值，避免重复编写多个CTE：

WITH target_integers AS (
    SELECT 2 AS target UNION ALL
    SELECT 50 UNION ALL
    SELECT 2000
)
SELECT MAX(t.decimal) AS decimal
FROM target_integers ti
LEFT JOIN your_table t ON t.decimal < ti.target
GROUP BY ti.target
ORDER BY ti.target;

这种写法的优势：

• 逻辑统一，新增或修改整数只需要调整target_integers部分
• 自动保持整数列表的顺序（通过ORDER BY ti.target）
• 若某个整数没有对应的小数（比如整数小于所有数据表中的值），会返回NULL，符合逻辑

也可以用窗口函数实现，适合需要保留更多中间数据的场景：

WITH target_integers AS (
    SELECT 2 AS target UNION ALL
    SELECT 50 UNION ALL
    SELECT 2000
),
ranked_decimals AS (
    SELECT 
        ti.target,
        t.decimal,
        ROW_NUMBER() OVER (PARTITION BY ti.target ORDER BY t.decimal DESC) AS rn
    FROM target_integers ti
    LEFT JOIN your_table t ON t.decimal < ti.target
)
SELECT decimal
FROM ranked_decimals
WHERE rn = 1
ORDER BY target;

处理长度未知的整数列表 #

当整数列表长度不确定时，需要根据数据库类型动态生成临时数据集：

1. MySQL 8.0+ #

将整数列表转为字符串，通过拆分函数生成临时表：

-- 替换为你的整数列表字符串
SET @integer_list = '2,50,2000';

WITH target_integers AS (
    SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@integer_list, ',', n), ',', -1) AS UNSIGNED) AS target
    FROM (
        -- 生成足够多的数字行（这里生成1-5，可根据实际需求扩展）
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) numbers
    WHERE n <= LENGTH(@integer_list) - LENGTH(REPLACE(@integer_list, ',', '')) + 1
)
SELECT MAX(t.decimal) AS decimal
FROM target_integers ti
LEFT JOIN your_table t ON t.decimal < ti.target
GROUP BY ti.target
ORDER BY ti.target;

2. PostgreSQL #

利用string_to_array和unnest函数拆分字符串：

-- 替换为你的整数列表字符串
WITH target_integers AS (
    SELECT unnest(string_to_array('2,50,2000', ','))::int AS target
)
SELECT MAX(t.decimal) AS decimal
FROM target_integers ti
LEFT JOIN your_table t ON t.decimal < ti.target
GROUP BY ti.target
ORDER BY ti.target;

3. SQL Server（表值参数方式） #

如果数据库支持表值参数，可以直接传入整数列表作为参数，无需字符串拆分：

-- 假设@integer_list是预先定义的表值参数
SELECT MAX(t.decimal) AS decimal
FROM @integer_list ti
LEFT JOIN your_table t ON t.decimal < ti.target
GROUP BY ti.target
ORDER BY ti.target;

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