从具有相同 ID 的项目中获取最高价值 [英] Get Highest value from items with same IDs

问题描述

我有一张这样的桌子:

ID      Severity    WorkItemSK
23636   3-Minor     695119
23636   3-Minor     697309
23647   2-Major     695081
23647   2-Major     694967

在这里，我有几个共享相同 ID 的工作项.如何获得具有最高 WorkItem 的唯一 ID?

In here I have several WorkItems that share the same ID. How can I get unique IDs that have the highest WorkItem?

所以它会是这样的:

ID      Severity    WorkItemSK
23636   3-Minor     697309
23647   2-Major     695081

帮助菜鸟 :) 介意提供一个线索，我应该使用哪些 SQL 命令(我又是菜鸟)?或者一个查询的例子?

Help the noob :) Mind giving a clue what SQL commands (again I am a noob) should I use? Or an example of a query?

先谢谢你！

推荐答案

假设 Severity 可以根据 WorkItemSK 变化，您将需要使用以下查询:

Assuming that Severity can change depending on the WorkItemSK, you'll want to use the following query:

Select  T.ID, T.Severity, T.WorkItemSK
From    Table T
Join
(
    Select  ID, Max(WorkItemSK) As WorkItemSK
    From    Table 
    Group By ID
) D On T.WorkItemSK = D.WorkItemSK And T.ID = D.ID

T.ID = D.ID的最后一个Join条件可能需要也可能不需要，取决于WorkItemSK是否可以出现多个表中的时间.

The last Join condition of T.ID = D.ID may or may not be needed, depending on whether WorkItemSK can appear multiple times in your table.

否则，你可以使用这个:

Otherwise, you can just use this:

Select  ID, Severity, Max(WorkItemSK) As WorkItemSK
From    Table 
Group by ID, Severity

但如果每个 ID 有不同的 Severity 值，您将看到重复的 ID.

But if you have different Severity values per ID, you'll see duplicate IDs.

这篇关于从具有相同 ID 的项目中获取最高价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！