在SQL Server 2014中，只有组后的值不同

Here is the sample of the data:

以下是数据样本:

 ID     Value   NumPeriod 
 ------------------------     
 1681642    596.8   2 
 1681642    596.8   3 
 1681663    445.4   2
 1681663    445.4   3 
 1681688    461.9   3 
 1681707    282.2   3 
 1681724    407.1   3
 1681743    467     2 
 1681743    467     3 
 1681767    502     3

I want to group by the [ID] and take only the distinct values of [Value] within each group and take the "first" distinct [Value] according to [NumPeriod]. So the result would look something this:

我要按[ID]分组，只取每个组内[Value]的不同值，并根据[NumPeriod]取“第一个”不同的[值]。结果是这样的

 ID     Value   NumPeriod 
 -------------------------     
 1681642    596.8   2 
 1681663    445.4   2
 1681688    461.9   3 
 1681707    282.2   3 
 1681724    407.1   3
 1681743    467     2 
 1681767    502     3

So I though something like this would work, but no luck:

所以我想这样的东西会有用，但是运气不好:

select 
    ID, distinct(Value), NumPeriod
from 
    MyTable
group by 
    ID, Value, NumPeriod
order by 
    ID, NumPeriod

Any help would be appreciated. Thanks!

如有任何帮助，我们将不胜感激。谢谢!

2 个解决方案

#1

You can use a ranking function and a CTE:

您可以使用等级函数和CTE:

WITH CTE AS
(
    SELECT ID, Value, NumPeriod,
           RN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY NumPeriod ASC)
    FROM MyTable
)
SELECT ID, Value, NumPeriod
FROM CTE
WHERE RN = 1
ORDER BY ID, Value

#2

I think all you have to change is where you call distinct. Try this:

我认为你所要改变的就是你所说的与众不同。试试这个:

select distinct ID, Value, NumPeriod
from MyTable
group by ID, Value, NumPeriod
order by ID, NumPeriod

#1