使用Partition By结合内连接返回单个值的技术求助

问题分析 #

你原来的SQL里，PARTITION BY another_number_field是错误的——这会把Table_B里每个不同的数值单独分成一个分区，每个分区的行号都是1，最后会返回所有大于7的数值，而非你要的最小那个。

解决方案 #

这里提供几种简单有效的写法：

方法1：直接用MIN聚合函数（最直观）

用MIN函数直接筛选出大于7的最小数值，一步到位：

SELECT MIN(b.another_number_field) AS target_number
FROM Table_A a
INNER JOIN Table_B b 
  ON b.another_number_field > a.number_field
WHERE a.number_field = 7;

这个写法会自动提取所有符合条件的数值，返回其中最小的11。

方法2：用窗口函数正确排序取第一条

如果一定要用窗口函数，无需按another_number_field分区，只需在关联后的结果里按数值升序排序，取行号为1的记录即可：

SELECT target_number
FROM (
    SELECT b.another_number_field AS target_number,
           ROW_NUMBER() OVER (ORDER BY b.another_number_field) AS rn
    FROM Table_A a
    INNER JOIN Table_B b 
      ON b.another_number_field > a.number_field
    WHERE a.number_field = 7
) ranked
WHERE rn = 1;

窗口函数会把所有符合条件的数值从小到大排序，行号1对应的就是最小的11。

方法3：用LIMIT/FETCH FIRST简化（适配多数数据库）

如果你的数据库支持LIMIT（如MySQL、PostgreSQL）或FETCH FIRST 1 ROW ONLY（如DB2、Oracle），可以直接排序后取第一条：

-- MySQL/PostgreSQL 写法
SELECT b.another_number_field AS target_number
FROM Table_A a
INNER JOIN Table_B b 
  ON b.another_number_field > a.number_field
WHERE a.number_field = 7
ORDER BY b.another_number_field
LIMIT 1;

-- DB2/Oracle 写法
SELECT b.another_number_field AS target_number
FROM Table_A a
INNER JOIN Table_B b 
  ON b.another_number_field > a.number_field
WHERE a.number_field = 7
ORDER BY b.another_number_field
FETCH FIRST 1 ROW ONLY;

扩展场景 #

如果Table_A里有多条符合条件的记录，需要为每条记录单独匹配对应的最小数值，可以给窗口函数加上分区条件（按Table_A的主键分区）：

SELECT a.number_field, t.target_number
FROM Table_A a
INNER JOIN (
    SELECT a.id, 
           b.another_number_field AS target_number,
           ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.another_number_field) AS rn
    FROM Table_A a
    INNER JOIN Table_B b 
      ON b.another_number_field > a.number_field
) t 
  ON a.id = t.id AND t.rn = 1
WHERE a.number_field = 7;

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