SQL查询同表指定行及与该行列值最接近的相邻记录实现方法

需求为传入行标识code，返回该code对应行的全列数据，同时返回同表中与该行Value列值最接近的、值更高的5条记录和值更低的5条记录。当前仅了解传入固定参考值的实现方式，不清楚参考值取自表内指定行时的SQL写法。

原有尝试代码 #

SELECT * FROM (SELECT code, value FROM table1 t1 WHERE code = x) AS a

UNION ALL

SELECT * FROM (SELECT * from table1 t2 WHERE NOT code = x AND count <= t1.count order by count DESC LIMIT 5) AS b

UNION ALL

SELECT * FROM (SELECT * from table1 t3 WHERE NOT code = x AND count <= t1.count order by count ASC LIMIT 5) AS c

原有代码问题 #

• 子查询b、c和子查询a为平级关系，无法直接引用t1.count字段，会触发字段不存在的报错
• 高低值筛选逻辑错误：两个子查询均使用count <= t1.count条件，未区分大于、小于基准值的场景
• 不符合UNION ALL语法要求：子查询a仅返回2列，子查询b、c返回全列，列数不匹配无法合并

测试数据 #

测试表包含Code、Value两列，测试数据如下：

预期结果示例 #

传入code=Australia时，由于不存在比15更小的值，返回该行及值最接近的4条更高值记录，共5条结果：

核心思路 #

• 第一步先根据传入的code查询到目标行，提取对应的Value作为筛选基准值，避免重复查询也解决跨子查询字段引用问题
• 分别筛选小于基准值的记录，按Value降序排列取前5条，即为离基准值最近的5条更低值记录
• 筛选大于基准值的记录，按Value升序排列取前5条，即为离基准值最近的5条更高值记录
• 合并三部分结果，最终按Value升序排列即可；如果需要控制总返回条数，在最外层加LIMIT限制即可匹配示例中的5条结果要求

可运行SQL（MySQL语法） #

WITH target AS (
    -- 传入的code参数替换此处的'Australia'即可
    SELECT Code, Value FROM table1 WHERE Code = 'Australia'
)
-- 查询目标行本身
SELECT Code, Value FROM target
UNION ALL
-- 查询最接近的5条更低值记录
(
    SELECT t.Code, t.Value
    FROM table1 t, target tr
    WHERE t.Code != tr.Code AND t.Value < tr.Value
    ORDER BY t.Value DESC
    LIMIT 5
)
UNION ALL
-- 查询最接近的5条更高值记录
(
    SELECT t.Code, t.Value
    FROM table1 t, target tr
    WHERE t.Code != tr.Code AND t.Value > tr.Value
    ORDER BY t.Value ASC
    LIMIT 5
)
-- 最终按Value升序排列，和预期输出顺序一致；需要控制总条数可加LIMIT 5
ORDER BY Value ASC;

逻辑说明 #

• 使用CTEtarget提前存储目标行数据，所有子查询都可以直接引用基准值，解决原代码的字段引用问题
• 所有UNION ALL分支返回的列数、字段类型完全一致，符合语法要求
• 当某一侧（更低/更高）的记录数不足5条时，会自动返回实际存在的所有记录，不会触发报错
• 自动排除code和目标行相同的记录，避免重复返回目标行

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