rank函数不满足需求时如何在SELECT查询中筛选符合求和条件的最优记录

你的需求属于固定条数约束下的最小代价（sum(B)最小）背包问题，目标是选6条记录，满足sum(A)≥150，且sum(B)尽可能小。因为原表已经按B升序排序，我们可以优先保留B更小的记录，仅当A总和不足时，用后面A更大、B仅略高的记录替换前面组合里贡献A最少的条目，从而得到最小sum(B)的结果。

通用实现代码（兼容PostgreSQL/MySQL 8.0+/SQL Server等支持CTE和窗口函数的数据库） #

-- 先给所有记录按B升序加行号
WITH ranked_records AS (
    SELECT 
        Name,
        A,
        B,
        ROW_NUMBER() OVER (ORDER BY B ASC) AS rn
    FROM your_table_name -- 替换为你实际的表名
),
-- 递归CTE枚举选6条的所有合法组合，跟踪已选条数、累计A、累计B、已选的最大行号（避免重复组合）
recursive_combinations AS (
    -- 初始状态：选1条记录
    SELECT
        1 AS selected_count,
        rn AS last_rn,
        A AS sum_A,
        B AS sum_B,
        CAST(Name AS CHAR(255)) AS selected_names
    FROM ranked_records
    UNION ALL
    -- 递归叠加，每次加1条行号更大的记录（保证B不小于之前的，避免重复计算）
    SELECT
        rc.selected_count + 1,
        rr.rn,
        rc.sum_A + rr.A,
        rc.sum_B + rr.B,
        CONCAT(rc.selected_names, ',', rr.Name)
    FROM recursive_combinations rc
    JOIN ranked_records rr ON rr.rn > rc.last_rn
    WHERE rc.selected_count < 6
)
-- 筛选选满6条、sumA≥150的记录，按sumB升序取第一条就是最优解
SELECT selected_names, sum_A, sum_B
FROM recursive_combinations
WHERE selected_count = 6 AND sum_A > 150
ORDER BY sum_B ASC
LIMIT 1;

示例场景简化优化方案 #

如果你的数据规模不大，且仅需要替换1-2条靠前记录即可满足A总和要求，可以使用更高效的简化写法，避免全量枚举：

WITH ranked AS (
    SELECT Name,A,B,ROW_NUMBER() OVER(ORDER BY B ASC) rn FROM your_table_name
),
-- 计算前5条的A总和、B总和
top5 AS (
    SELECT SUM(A) sumA_top5, SUM(B) sumB_top5 FROM ranked WHERE rn <=5
)
-- 前5条加后面任意1条，筛选sumA≥150的，按sumB升序取第一条就是最优结果
SELECT 
    CONCAT(GROUP_CONCAT(CASE WHEN rn <=5 THEN Name END ORDER BY rn),',',r.Name) AS selected_names,
    t.sumA_top5 + r.A AS total_A,
    t.sumB_top5 + r.B AS total_B
FROM ranked r, top5 t
WHERE r.rn >5
HAVING total_A >150
ORDER BY total_B ASC
LIMIT 1;

运行上述简化代码，你给出的示例数据会直接返回Name1,Name2,Name3,Name4,Name5,Name7，total_A=151，total_B=22，完全符合需求。

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