Access中使用SQL匹配另一表中小于原百分比的最接近值

需求明确 #

• Table1（用户表）：包含NAME（姓名）、PERCENTAGE（百分比）字段，现有数据：
  • Chris | 56%
  • Jane | 67%
• Table2（匹配规则表）：包含PERCENTAGE（阈值百分比）、VALUE（对应值）字段，现有数据：
  • 50% | £3
  • 60% | £4
  • 70% | £5
• 核心需求：为Table1的每条记录，从Table2中找到小于当前记录百分比的最大阈值百分比，返回对应的VALUE，最终输出格式为NAME、PERCENTAGE（来自Table1）、VALUE（来自Table2）。

方案1：子查询实现（通用兼容） #

这种写法适用于大部分主流数据库（MySQL、PostgreSQL、SQLite等），逻辑直接易懂：

SELECT 
    t1.NAME,
    t1.PERCENTAGE,
    (SELECT t2.VALUE
     FROM Table2 t2
     -- 转换百分比为数值比较，避免字符串排序问题
     WHERE CAST(REPLACE(t2.PERCENTAGE, '%', '') AS DECIMAL) < CAST(REPLACE(t1.PERCENTAGE, '%', '') AS DECIMAL)
     ORDER BY CAST(REPLACE(t2.PERCENTAGE, '%', '') AS DECIMAL) DESC
     LIMIT 1) AS VALUE
FROM Table1 t1;

• 逻辑说明：对Table1的每条记录，筛选Table2中数值更小的阈值百分比，按阈值降序排列后取第一条的VALUE，即为最接近的匹配值。
• 注意：如果数据库中PERCENTAGE已经是数值类型（比如存储为56、67而非带%的字符串），可直接用字段比较，去掉CAST(REPLACE(...) AS DECIMAL)部分。

方案2：窗口函数实现（高效适配大数据量） #

如果使用支持窗口函数的数据库（PostgreSQL、SQL Server、MySQL 8.0+等），可以用以下写法，避免每条记录单独执行子查询，性能更优：

SELECT 
    t1.NAME,
    t1.PERCENTAGE,
    t2.VALUE
FROM Table1 t1
JOIN (
    SELECT 
        t2_inner.PERCENTAGE,
        t2_inner.VALUE,
        -- 用LEAD函数获取下一个更大的阈值百分比
        LEAD(CAST(REPLACE(t2_inner.PERCENTAGE, '%', '') AS DECIMAL)) OVER (ORDER BY CAST(REPLACE(t2_inner.PERCENTAGE, '%', '') AS DECIMAL)) AS next_threshold
    FROM Table2 t2_inner
) t2 ON 
    CAST(REPLACE(t1.PERCENTAGE, '%', '') AS DECIMAL) > CAST(REPLACE(t2.PERCENTAGE, '%', '') AS DECIMAL)
    AND (
        CAST(REPLACE(t1.PERCENTAGE, '%', '') AS DECIMAL) <= t2.next_threshold
        OR t2.next_threshold IS NULL -- 匹配最大的阈值（没有更大的阈值时）
    );

• 逻辑说明：先给Table2的每个阈值标记出下一个更大的阈值，再将Table1的百分比与这些阈值区间匹配，找到对应的VALUE。

输出结果 #

两种方案最终都会得到如下结果：

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