简短的回答是,没有简单的方法可以通过查询来实现这一点。您需要转换数据,然后以这种方式确定最大值。比如:
Select Id, ColumnName, Value
From (
Select '1' As ColumnName, Id, [1] As Value
From Table
Union All
Select '2', Id, [2]
From Table
Union All
Select '3', Id, [3]
From Table
) As Z
Where Exists(
Select 1
From (
Select '1' As ColumnName, Id, [1] As Value
From Table
Union All
Select '2', Id, [2]
From Table
Union All
Select '3', Id, [3]
From Table
) As Z2
Where Z2.Id = Z.Id
Group By Z2.Id
Having Max(Z2.Value) = Z.Value
)
Order By Id
这个解决方案依赖于一组固定的列,在这些列中,您基本上命名了union all查询中的列。此外,如果有两列具有相同ID的相同值,则会得到重复的行。