如何获取MAX()/MIN()值所在行的ResultID？SQL技术求助

你好！我看你已经把UK和US的测试数据合并到一起了，现在的核心问题是要精准关联回最高分、最低分对应的原始ResultID，同时避免重复和错误。这里有个更简洁高效的方案，利用窗口函数来定位目标行，完全符合你的需求：

WITH Combined_Results AS (
    -- 合并UK和US测试数据，保留原始ResultID和测试日期
    SELECT 
        ResultID,
        Test_UK AS TestID,
        Test_UK_Scr AS TestScore,
        TestTakenOn AS TestDate
    FROM Results
    UNION ALL
    SELECT 
        ResultID,
        Test_US AS TestID,
        Test_US_Scr AS TestScore,
        TestTakenOn AS TestDate
    FROM Results
),
Rank_Results AS (
    -- 对每个TestID，分别按分数降序、升序排名，标记出最高分和最低分的行
    SELECT 
        TestID,
        TestScore,
        ResultID,
        TestDate,
        -- 降序排名：1代表当前TestID的最高分
        ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY TestScore DESC) AS top_rank,
        -- 升序排名：1代表当前TestID的最低分
        ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY TestScore ASC) AS low_rank,
        -- 直接计算每个TestID的最后测试日期
        MAX(TestDate) OVER (PARTITION BY TestID) AS LastDateTestTaken
    FROM Combined_Results
)
-- 关联最高分和最低分的记录，输出最终结果
SELECT 
    top_rec.TestID,
    top_rec.TestScore AS TopScore,
    low_rec.TestScore AS LowScore,
    top_rec.LastDateTestTaken,
    top_rec.ResultID AS MaxValueLocID,
    low_rec.ResultID AS MinValueLocID
FROM Rank_Results top_rec
INNER JOIN Rank_Results low_rec
    ON top_rec.TestID = low_rec.TestID
    AND top_rec.top_rank = 1
    AND low_rec.low_rank = 1
GROUP BY 
    top_rec.TestID,
    top_rec.TestScore,
    low_rec.TestScore,
    top_rec.LastDateTestTaken,
    top_rec.ResultID,
    low_rec.ResultID
ORDER BY top_rec.TestID;

方案细节说明： #

• Combined_Results：和你原来的思路一致，把UK和US的测试数据合并成统一结构，确保每条记录都关联到原始的ResultID，这是后续关联的基础。
• Rank_Results：
  • 使用ROW_NUMBER()窗口函数，对每个TestID的分数分别做降序和升序排名，排名为1的行就是该TestID的最高分和最低分对应的记录。
  • 用MAX(TestDate) OVER (PARTITION BY TestID)直接计算每个TestID的最后测试日期，不需要额外分组查询，效率更高。
• 最终关联：通过INNER JOIN把每个TestID的最高分记录（top_rank=1）和最低分记录（low_rank=1）关联起来，一次性获取所有需要的字段。

关于你原方案的问题： #

你之前的CROSS APPLY条件存在逻辑错误（比如误把Test_US_Scr写成Test_UK_Scr），而且没有限定只匹配最高分或最低分的单一条件，导致返回了大量无关的ResultID，进而出现重复数据。这个窗口函数的方法只需要扫描合并后的数据集一次，不仅更精准，性能也更优。

如果遇到多个行有相同最高分/最低分的情况，ROW_NUMBER()会随机返回其中一个的ResultID；如果需要返回所有同分的ResultID，可以把ROW_NUMBER()换成RANK()，然后调整最终查询的筛选逻辑即可。

内容的提问来源于stack exchange，提问作者Mr.Softy