Oracle SQL如何为B表每一行查询A表对应行的上下各10条数据

首先明确相邻记录的判定规则，以下方案默认按同ID分组内，no序号升序排序判定前后，你可以根据实际需求调整排序字段为Time，或去掉分组规则做全表排序。

之前你用lag()、lead()未得到预期结果的核心原因是：这两个函数用于取当前行指定偏移量的单个字段值，并非直接提取多行记录。如果用这两个函数实现需求需要定义20个偏移量函数再做行转列，复杂度高且性能差，不推荐使用。

推荐实现方案（兼容所有Oracle版本，大数据量下性能最优） #

实现逻辑：

• 先给Table A的所有记录按分组规则生成排序行号
• 关联Table B拿到每个匹配行在Table A中的行号
• 按行号范围过滤得到前后10条相邻记录

WITH a_rn AS (
    -- 给Table A按ID分组、按序号升序排序生成唯一行号，排序规则可按需调整为Time字段
    SELECT 
        no,
        id,
        time,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY no ASC) AS rn
    FROM table_a
),
b_match AS (
    -- 关联拿到Table B每行在Table A中的对应行号，匹配规则可按需调整为ID+Time匹配
    SELECT 
        b.*,
        a_rn.rn AS match_rn
    FROM table_b b
    INNER JOIN a_rn 
        ON b.id = a_rn.id 
        AND b.no = a_rn.no
)
-- 最终取匹配行前后10条记录
SELECT 
    a_rn.no,
    a_rn.id,
    a_rn.time,
    b_match.no AS match_source_no, -- 标记该相邻记录属于B表哪一行的匹配结果
    b_match.time AS match_source_time
FROM a_rn
INNER JOIN b_match 
    ON a_rn.id = b_match.id 
    AND a_rn.rn BETWEEN (b_match.match_rn - 10) AND (b_match.match_rn + 10)
-- 若不需要返回B表本身的匹配行，可取消下面这行的注释
-- AND a_rn.rn != b_match.match_rn
ORDER BY a_rn.id, a_rn.rn;

性能优化建议 #

• 给Table A的(ID, no)（如果按Time排序则为(ID, Time)）建立联合索引，可避免全表扫描，大幅提升窗口函数计算效率
• 若Table B数据量较小，可先对Table B的匹配字段做去重处理，减少关联计算量

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