时间戳

首页 > 解决方案 > Sql根据其他两个ID的两列获取MAX和MIN值

sql - Sql根据其他两个ID的两列获取MAX和MIN值

问题描述

我在想出一个查询时遇到困难，有人能帮我解决这个问题吗？问题：4 列代表 2 个单独测试的结果。其中一个在英国拍摄，另一个在美国拍摄。他们两个是同一个测试，我需要找到在这两个国家参加的测试的最高和最低分数。我还需要避免使用子查询和临时表。将欣赏该问题的理论思想和实际解决方案。

该表如下所示：

 ResultID   Test_UK   Test_US   Test_UK_Score   Test_US_Score
   1          1         2       48              11
   2          4         1       21              24
   3          3         1       55              71
   4          5         6       18              78
   5          7         4       19              49
   6          1         3       23              69
   7          5         2       98              35
   8          6         7       41              47

我正在寻找的预期结果：

TestID  HighestScore  LowestScore
   1       71             23
   2       35             11
   3       69             55
   4       49             21
   5       98             18
   6       78             41
   7       47             19

我尝试实现一个比较案例，但我仍然使用子查询来提取最终结果。也尝试过联合，但它再次出现在子查询中。据我所知，在查询时应该是一个案例，但不能真正想出它的逻辑，因为它需要匹配测试的 ID。谢谢！我尝试过并得到了最好的结果（仍然错误）

select v.TestID,
    max(case when Test_US_Score > Test_UK_Score then Test_UK_Score else null end) MaxS, 
    min(case when Test_UK_Score > Test_US_Score then Test_US_Score else null end) MinS
FROM ResultsDB rDB CROSS APPLY
    (VALUES (Test_UK, 1), (Test_US, 0)
    ) V(testID, amount)
GROUP BY v.TestID

额外的

M. Kanarkowski提供的答案是一个完美的解决方案。我不是 CTE 方面的专家，并且有点困惑，如何调整此查询以返回找到 min 和 max 的行的结果 ID。

像这样的东西：

TestID Result_ID_Max Result_ID_Min
  1          3              6
  2          7              1 
  3          6              3

额外2

查询的期望结果将是这样的。最后两列表示原始表中找到最大值和最小值的行的 ID。

TestID HighestScore LowestScore Result_ID_Of_Max Result_ID_Of_Min
  1         71           23            3              6
  2         35           11            7              1 
  3         69           55            6              3

标签： sqlsql-serverdatabasetsql

解决方案

例如，您可以使用union两个国家/地区的结果，然后为您的数据选择最大值和最小值。

with cte as (
    select Test_UK as TestID, Test_UK_Score as score from yourTable
    union all 
    select Test_US as TestID, Test_US_Score as score from yourTable
)
select
    TestID
    ,max(score) as HighestScore
    ,min(score) as LowestScore
from cte
group by TestID
order by TestID

额外：我假设您希望在前面的结果中添加额外的列。如果不只是采取上述选择和替换Test_UK_Score和Test_US_Score与ResultID。

with cte as (
    select Test_UK as TestID, Test_UK_Score as score, ResultID from yourTable
    union all 
    select Test_US as TestID, Test_US_Score as score, ResultID from yourTable
)
select
    TestID
    ,max(score) as HighestScore
    ,min(score) as LowestScore
    ,max(ResultID) as Result_ID_Max
    ,min(ResultID) as Result_ID_Min
from cte
group by TestID
order by TestID

推荐阅读