如何在MS Access查询行中对7个数值里的最小5个求和

我懂你想在MS Access里实现类似Excel中SUM(SMALL(range, {1,2,3,4,5}))的功能——对单条记录的7个指定字段取最小的5个数值求和。咱们直接看可行的实现方法，先拿你给的示例数据来验证：

方法一：总和减去最大的两个值（简洁高效） #

既然要取7个里最小的5个，等价于所有字段的总和减去最大的两个字段值之和。这种写法不需要额外子查询，直接在查询的计算字段里完成：

假设你的表名为StudentScores，可以写这样的SQL查询：

SELECT
    history,
    geography,
    physics,
    mathematics,
    agriculture,
    science,
    Civics,
    -- 计算最小5个的和：总和 - 最大两个值的和
    (history + geography + physics + mathematics + agriculture + science + Civics)
    -
    (
        -- 取7个字段中的最大值
        Max(history, geography, physics, mathematics, agriculture, science, Civics)
        +
        -- 取排除最大值后的剩余字段中的最大值（即第二大值）
        Switch(
            history = Max(history, geography, physics, mathematics, agriculture, science, Civics), Max(geography, physics, mathematics, agriculture, science, Civics),
            geography = Max(history, geography, physics, mathematics, agriculture, science, Civics), Max(history, physics, mathematics, agriculture, science, Civics),
            physics = Max(history, geography, physics, mathematics, agriculture, science, Civics), Max(history, geography, mathematics, agriculture, science, Civics),
            mathematics = Max(history, geography, physics, mathematics, agriculture, science, Civics), Max(history, geography, physics, agriculture, science, Civics),
            agriculture = Max(history, geography, physics, mathematics, agriculture, science, Civics), Max(history, geography, physics, mathematics, science, Civics),
            science = Max(history, geography, physics, mathematics, agriculture, science, Civics), Max(history, geography, physics, mathematics, agriculture, Civics),
            Civics = Max(history, geography, physics, mathematics, agriculture, science, Civics), Max(history, geography, physics, mathematics, agriculture, science)
        )
    ) AS SumBottom5
FROM StudentScores;

拿你的示例数据计算：总和是1+2+4+5+6+7+3=28，最大两个值是7和6，28-13=15，正好是最小5个值1+2+3+4+5的和，结果正确。

方法二：将列转成行后取最小5个求和（灵活易扩展） #

如果你的字段数量可能变化，或者需要处理Null值，这种方法更灵活。通过UNION ALL把每条记录的7个字段转换成7行数据，再按记录分组取最小的5个求和：

SELECT
    t.ID, -- 假设你的表有主键ID用来标识每条记录
    t.history,
    t.geography,
    t.physics,
    t.mathematics,
    t.agriculture,
    t.science,
    t.Civics,
    Sum(sub.ScoreValue) AS SumBottom5
FROM
    StudentScores t
INNER JOIN
    (
        -- 把每个字段转成一行数据
        SELECT ID, history AS ScoreValue FROM StudentScores
        UNION ALL
        SELECT ID, geography AS ScoreValue FROM StudentScores
        UNION ALL
        SELECT ID, physics AS ScoreValue FROM StudentScores
        UNION ALL
        SELECT ID, mathematics AS ScoreValue FROM StudentScores
        UNION ALL
        SELECT ID, agriculture AS ScoreValue FROM StudentScores
        UNION ALL
        SELECT ID, science AS ScoreValue FROM StudentScores
        UNION ALL
        SELECT ID, Civics AS ScoreValue FROM StudentScores
    ) sub ON t.ID = sub.ID
WHERE sub.ScoreValue IS NOT NULL -- 排除Null值（如果有的话）
GROUP BY t.ID, t.history, t.geography, t.physics, t.mathematics, t.agriculture, t.science, t.Civics
HAVING Count(sub.ScoreValue) >=5 -- 确保至少有5个有效数值
ORDER BY t.ID;

这种方法的优势是：如果后续字段增加或减少，只需要修改UNION ALL部分即可；遇到Null值时也能自动过滤，避免影响求和结果。

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