Over Partition for set column SQL

Question

我有这个表，我需要设置ID列= 1为列分钟的最大值，其余ID列= 0

初始表:

Register |minutes  | ID
10       |5        | 0
10       |6        | 0
10       |0        | 0     
12       |3        | 0
12       |0        | 0
12       |4        | 0

最终表:

Register |minutes  | ID
10       |5        | 0
10       |6        | 1
10       |0        | 0     
12       |3        | 0
12       |0        | 0
12       |4        | 1

使用Over Partition，你知道吗?

UPDATE A
SET ID = 1
FROM
(
Select top 1 row_number() over (PARTITION BY     minutes 
order by minutes asc)   AS column,*
from table
)A                  
WHERE A.column=1

Answer 1

您可以在可更新的CTE中使用row_number():

with m as (
select *, 
row_number() over(partition by register order by minutes desc) rn
from t
)
update m set id=1 where rn=1

Answer 2

这是你想要的吗?

DECLARE @max INT
SELECT  TOP 1
@max = Minutes
FROM    YourTable
ORDER BY Minutes DESC
UPDATE YourTable
SET     ID = CASE       
WHEN Minutes = @max
THEN 1
ELSE 0
END

Answer 3

如果您不想使用CTE或变量表:

UPDATE A
SET A.ID = CASE       
WHEN B.RowNumber = 1
THEN 1
ELSE 0
END
FROM table A
JOIN (
SELECT *, row_number() over (PARTITION BY Register 
order by minutes DESC) AS RowNumber
FROM table
) B ON A.Register = B.Register AND A.minutes = B.minutes

Answer 4

我保留了之前的答案，因为它代表了问题的答案，因为它是在回答的时候。给定添加到问题中的新信息，更新查询如下所示:

;WITH MyCTE AS
(
SELECT  Register,
Minutes,
ID,
ROW_NUMBER() OVER (PARTITION BY Register ORDER BY Minutes DESC) RowN
FROM    YourTable
)
UPDATE  MyCTE
SET     ID = CASE
WHEN RowN = 1 THEN 1
ELSE 0
END