MS Access中急救竞赛个人最高分跨记录对比技术问询

核心思路 #

• 原表中队员得分采用横向列存储（FAY01Score至FAY04Score），无法直接全局对比，需先将这些列转换为纵向行记录，让所有队员的得分处于同一字段中
• 基于转换后的行记录，获取全局最高分，再筛选出所有得分等于该最高分的队员，同时关联获取姓名与组织信息

分步实现SQL（适用于Access数据库） #

第一步：将横向队员数据转为纵向行记录 #

通过UNION ALL把每队的4名队员拆分为独立行，保留组织信息：

SELECT 
    t.FAYOrgID,
    t.FAY01Name AS CompetitorID,
    t.FAY01Score AS IndividualScore,
    o.OrgName,
    o.OrgNo
FROM tblFirstAidYouth t
JOIN tblOrgs o ON t.FAYOrgID = o.OrgID
WHERE t.FAY01Score IS NOT NULL -- 排除无得分的队员
UNION ALL
SELECT 
    t.FAYOrgID,
    t.FAY02Name AS CompetitorID,
    t.FAY02Score AS IndividualScore,
    o.OrgName,
    o.OrgNo
FROM tblFirstAidYouth t
JOIN tblOrgs o ON t.FAYOrgID = o.OrgID
WHERE t.FAY02Score IS NOT NULL
UNION ALL
SELECT 
    t.FAYOrgID,
    t.FAY03Name AS CompetitorID,
    t.FAY03Score AS IndividualScore,
    o.OrgName,
    o.OrgNo
FROM tblFirstAidYouth t
JOIN tblOrgs o ON t.FAYOrgID = o.OrgID
WHERE t.FAY03Score IS NOT NULL
UNION ALL
SELECT 
    t.FAYOrgID,
    t.FAY04Name AS CompetitorID,
    t.FAY04Score AS IndividualScore,
    o.OrgName,
    o.OrgNo
FROM tblFirstAidYouth t
JOIN tblOrgs o ON t.FAYOrgID = o.OrgID
WHERE t.FAY04Score IS NOT NULL

第二步：获取全局最高分并筛选对应队员 #

基于上述转换结果，嵌套查询获取最高分，再关联tblFAYCompetitors拿到队员真实姓名：

SELECT 
    c.CompetitorName AS 队员姓名,
    sub.IndividualScore AS 个人得分,
    sub.OrgName AS 所属组织,
    sub.OrgNo AS 组织编号
FROM (
    -- 子查询：所有队员的得分及组织信息
    SELECT 
        t.FAYOrgID,
        t.FAY01Name AS CompetitorID,
        t.FAY01Score AS IndividualScore,
        o.OrgName,
        o.OrgNo
    FROM tblFirstAidYouth t
    JOIN tblOrgs o ON t.FAYOrgID = o.OrgID
    WHERE t.FAY01Score IS NOT NULL
    UNION ALL
    SELECT 
        t.FAYOrgID,
        t.FAY02Name AS CompetitorID,
        t.FAY02Score AS IndividualScore,
        o.OrgName,
        o.OrgNo
    FROM tblFirstAidYouth t
    JOIN tblOrgs o ON t.FAYOrgID = o.OrgID
    WHERE t.FAY02Score IS NOT NULL
    UNION ALL
    SELECT 
        t.FAYOrgID,
        t.FAY03Name AS CompetitorID,
        t.FAY03Score AS IndividualScore,
        o.OrgName,
        o.OrgNo
    FROM tblFirstAidYouth t
    JOIN tblOrgs o ON t.FAYOrgID = o.OrgID
    WHERE t.FAY03Score IS NOT NULL
    UNION ALL
    SELECT 
        t.FAYOrgID,
        t.FAY04Name AS CompetitorID,
        t.FAY04Score AS IndividualScore,
        o.OrgName,
        o.OrgNo
    FROM tblFirstAidYouth t
    JOIN tblOrgs o ON t.FAYOrgID = o.OrgID
    WHERE t.FAY04Score IS NOT NULL
) AS sub
JOIN tblFAYCompetitors c ON sub.CompetitorID = c.ID
WHERE sub.IndividualScore = (
    -- 子查询：获取所有队员中的最高分
    SELECT MAX(IndividualScore)
    FROM (
        SELECT FAY01Score AS IndividualScore FROM tblFirstAidYouth WHERE FAY01Score IS NOT NULL
        UNION ALL
        SELECT FAY02Score AS IndividualScore FROM tblFirstAidYouth WHERE FAY02Score IS NOT NULL
        UNION ALL
        SELECT FAY03Score AS IndividualScore FROM tblFirstAidYouth WHERE FAY03Score IS NOT NULL
        UNION ALL
        SELECT FAY04Score AS IndividualScore FROM tblFirstAidYouth WHERE FAY04Score IS NOT NULL
    ) AS allScores
)
ORDER BY sub.IndividualScore DESC, sub.OrgName;

关键说明 #

• 加入IS NOT NULL判断，避免空值干扰最高分计算
• UNION ALL保留所有重复行，确保同队多名并列最高分的队员都能被筛选出来
• 最终结果可直接用于生成包含队员姓名、得分、所属组织的报表

内容的提问来源于stack exchange，提问作者Kevin Ashcroft-Johnson