SQL Server - 如果行是组中的第一条记录，则更新列 [英] SQL Server - Update a column if row is the first record in group

问题描述

我如何识别对每个品牌和类别有价值的第一行，然后将列FirstValue更新为1，否则0？
例如预期的表格

How do i identify the first row that has value for each Brand and Category, then update Column "FirstValue" as 1, else 0? e.g of expected table

Date   |  Brands  |  Category |  Value  | FirstValue 
Jan 08 |   A      |  1        | 0       |0
Jan 08 |   A      |  2        | 0       |0
Jan 08 |   A      |  3        | 0       |0
Jan 08 |   B      |  1        | 12      |1
Jan 08 |   B      |  2        | 0       |0
Jan 08 |   B      |  3        | 0       |0
Feb 08 |   A      |  1        | 5       |1
Feb 08 |   A      |  2        | 0       |0
Feb 08 |   A      |  3        | 67      |1
Feb 08 |   B      |  1        | 0       |0
Feb 08 |   B      |  2        | 0       |0
Feb 08 |   B      |  3        | 6       |1

推荐答案

通用表表达式实际上是可更新的，并且将CTE与排名函数结合 ROW_NUMBER（） 给出了一个完美的解决方案： p>

Common table expressions are actually updatable, and combining CTEs with ranking functions ROW_NUMBER() gives a perfect solution:

with cte as (
 select row_number() over (partition by Brand, Category order by Date) as rn
, FirstValue
from Table)
update cte
set FirstValue = case when rn = 1 then 1 else 0 end;

这篇关于SQL Server - 如果行是组中的第一条记录，则更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！