Oracle SQL如何实现相邻记录SIZE和为4的自定义排序

该需求无法通过单个Oracle内置排序函数直接实现，但可以通过Oracle 11gR2及以上版本原生支持的递归公用表表达式（递归CTE）、窗口函数组合完成，无需自定义函数或存储过程。

首先需要说明：你给出的示例数据不存在所有相邻记录SIZE之和均为4的排列。推导逻辑很简单：5条记录共4组相邻对，若每组和均为4，相邻对总和为16；而线性序列的相邻对总和 = 2所有记录SIZE总和 - 首记录SIZE - 尾记录SIZE。示例中SIZE总和为13，可推导出首+尾=213 -16=10，但示例中最大的两个值为5和3，和仅为8，无法满足要求。你提供的参考排序实际是「尽可能多匹配相邻和为4」的结果，最多可满足2组相邻对的和为4。

实现代码 #

1. 构造测试数据 #

CREATE TABLE PACKAGES (ID NUMBER, NAME VARCHAR2(20), SIZE NUMBER);
INSERT INTO PACKAGES VALUES (1,'PACKAGE A',3);
INSERT INTO PACKAGES VALUES (2,'PACKAGE B',2);
INSERT INTO PACKAGES VALUES (3,'PACKAGE C',1);
INSERT INTO PACKAGES VALUES (4,'PACKAGE D',2);
INSERT INTO PACKAGES VALUES (5,'PACKAGE E',5);
COMMIT;

2. 严格匹配所有相邻和为4的实现 #

如果你的实际数据存在满足所有相邻和为4的排列，可以使用以下代码查询：

WITH TOTAL_CNT AS (
    SELECT COUNT(*) AS TOTAL FROM PACKAGES
),
RECURSIVE_SORT (ID, NAME, SIZE, PATH, USED_IDS, LVL) AS (
    -- 锚点：所有记录均可作为序列起点
    SELECT 
        ID,
        NAME,
        SIZE,
        TO_CHAR(ID) AS PATH,
        ',' || ID || ',' AS USED_IDS,
        1 AS LVL
    FROM PACKAGES
    UNION ALL
    -- 递归：仅选取未使用、且与当前记录SIZE和为4的记录
    SELECT 
        P.ID,
        P.NAME,
        P.SIZE,
        R.PATH || '->' || P.ID AS PATH,
        R.USED_IDS || P.ID || ',' AS USED_IDS,
        R.LVL + 1 AS LVL
    FROM RECURSIVE_SORT R
    JOIN PACKAGES P 
        ON R.SIZE + P.SIZE = 4
        AND R.USED_IDS NOT LIKE '%,' || P.ID || ',%'
    WHERE R.LVL < (SELECT TOTAL FROM TOTAL_CNT)
)
-- 取出覆盖所有记录的合法序列，按路径顺序返回结果
SELECT 
    P.ID, 
    P.NAME, 
    P.SIZE
FROM RECURSIVE_SORT R
JOIN PACKAGES P ON INSTR(',' || R.PATH || ',', ',' || P.ID || ',') > 0
WHERE R.LVL = (SELECT TOTAL FROM TOTAL_CNT)
ORDER BY INSTR(',' || R.PATH || ',', ',' || P.ID || ',');

3. 优先匹配相邻和为4的实现（适配示例场景） #

如果需要优先满足尽可能多的相邻和为4，剩余记录按顺序拼接（即你给出的参考结果逻辑），可以使用以下代码：

WITH TOTAL_CNT AS (
    SELECT COUNT(*) AS TOTAL FROM PACKAGES
),
RECURSIVE_SORT (ID, NAME, SIZE, PATH, USED_IDS, LVL, MATCH_CNT) AS (
    SELECT 
        ID,
        NAME,
        SIZE,
        TO_CHAR(ID) AS PATH,
        ',' || ID || ',' AS USED_IDS,
        1 AS LVL,
        0 AS MATCH_CNT
    FROM PACKAGES
    UNION ALL
    SELECT 
        P.ID,
        P.NAME,
        P.SIZE,
        R.PATH || '->' || P.ID AS PATH,
        R.USED_IDS || P.ID || ',' AS USED_IDS,
        R.LVL + 1 AS LVL,
        R.MATCH_CNT + CASE WHEN R.SIZE + P.SIZE = 4 THEN 1 ELSE 0 END
    FROM RECURSIVE_SORT R
    JOIN PACKAGES P ON R.USED_IDS NOT LIKE '%,' || P.ID || ',%'
    WHERE R.LVL < (SELECT TOTAL FROM TOTAL_CNT)
)
SELECT 
    P.ID, 
    P.NAME, 
    P.SIZE
FROM (
    SELECT PATH,
           ROW_NUMBER() OVER (ORDER BY MATCH_CNT DESC, PATH) AS RN
    FROM RECURSIVE_SORT
    WHERE LVL = (SELECT TOTAL FROM TOTAL_CNT)
) R
JOIN PACKAGES P ON INSTR(',' || R.PATH || ',', ',' || P.ID || ',') > 0
WHERE R.RN = 1
ORDER BY INSTR(',' || R.PATH || ',', ',' || P.ID || ',');

运行该代码即可得到与参考结果逻辑一致的排序输出。

注意事项 #

• 该类排序本质是哈密顿路径求解问题，属于NP类问题，当记录数超过15条时，递归枚举的性能会快速下降，建议根据实际业务数据增加剪枝条件优化。
• 如果存在多组符合要求的排序结果，可以通过调整ROW_NUMBER的排序规则选取需要的结果。
• 递归CTE为Oracle 11gR2版本开始支持的原生功能，覆盖目前所有官方维护的Oracle数据库版本，无需额外安装组件。

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