本文介绍了SQL Server - 如果行是组中的第一条记录,则更新列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我如何识别对每个品牌和类别有价值的第一行,然后将列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与排名函数结合给出了一个完美的解决方案: 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 - 如果行是组中的第一条记录,则更新列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!