使用IN选取多行，无匹配时选取最近的小于目标值的元素

问题背景 #

现有数据表：

需要将其与目标向量 (2, 4, 6) 对齐，实现前向填充（Frontfill）：若目标值不在原表的Value1列中，选取Value1小于该目标值的最大行数据。期望输出：

直接用 WHERE Value1 IN (2,4,6) 只能得到无填充的结果：

单值场景可以通过 WHERE Value1 <= X ORDER BY Value1 DESC LIMIT 1 实现，但多值场景下需要避免循环，高效完成该操作，此需求用于金融数据与日期向量的对齐。

解决方案1：目标值表关联 + 聚合函数 #

先将目标向量转为临时表/子查询，再与原表关联，找到每个目标值对应的最大Value1，最后关联回原表获取对应Value2。

示例SQL：

WITH target_values AS (
    SELECT 2 AS target UNION ALL
    SELECT 4 AS target UNION ALL
    SELECT 6 AS target
)
SELECT 
    tv.target AS Value1,
    t.Value2
FROM target_values tv
LEFT JOIN (
    SELECT 
        tv.target,
        MAX(t.Value1) AS max_value1
    FROM target_values tv
    JOIN your_table t ON t.Value1 <= tv.target
    GROUP BY tv.target
) max_vals ON tv.target = max_vals.target
JOIN your_table t ON t.Value1 = max_vals.max_value1
ORDER BY tv.target;

解决方案2：窗口函数（适配PostgreSQL、MySQL 8+、SQL Server等） #

通过LEFT JOIN关联原表与目标值表，使用ROW_NUMBER()窗口函数为每个目标值筛选出符合条件的最大Value1行。

示例SQL：

WITH target_values AS (
    SELECT 2 AS target UNION ALL
    SELECT 4 AS target UNION ALL
    SELECT 6 AS target
), ranked_data AS (
    SELECT 
        tv.target AS Value1,
        t.Value2,
        ROW_NUMBER() OVER (PARTITION BY tv.target ORDER BY t.Value1 DESC) AS rn
    FROM target_values tv
    LEFT JOIN your_table t ON t.Value1 <= tv.target
)
SELECT Value1, Value2
FROM ranked_data
WHERE rn = 1
ORDER BY Value1;

注意事项 #

• 两种方案均为批量集合操作，避免了循环遍历，适合金融数据这类大规模日期/数值对齐场景
• 若目标向量为动态输入，可将target_values替换为参数化临时表或表变量（不同数据库语法略有差异）
• 务必给your_table的Value1字段创建索引，能大幅提升关联查询效率，尤其数据量较大时

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