表中有三个字段 T1,T2,T3数据
T1 T2 T3
2010-01-01 A
2010-01-01 B
2010-01-12 A
2010-01-12 A
2010-01-12 A能不能使T3的数据如果是同一天的前面年月显示一样,后面的二位数从001开始增加,更新结果如下T1 T2 T3
2010-01-11 A 201001001
2010-01-11 B 201001002
2010-08-12 A 201008001
2010-08-12 A 201008002
2010-11-12 A 201011001
如果写这个更新SQL语句!
T1 T2 T3
2010-01-01 A
2010-01-01 B
2010-01-12 A
2010-01-12 A
2010-01-12 A能不能使T3的数据如果是同一天的前面年月显示一样,后面的二位数从001开始增加,更新结果如下T1 T2 T3
2010-01-11 A 201001001
2010-01-11 B 201001002
2010-08-12 A 201008001
2010-08-12 A 201008002
2010-11-12 A 201011001
如果写这个更新SQL语句!
T3=Rtrim(T1)+Right(1000+ row_number() over (partition by T1 order by T2),3)
from 表
with cte as (
select t1,t2,t3=row_number()over(partition by t1 order by t2) from tb)select t1,t2,convert(char(6),t1,112)+right('00'+cast(t3 as varchar),3) as t3
from cte
(select t1,t2,
convert(char(6),t1,112)+right('00'+cast(row_number()over(partition by t1 order by t2) as varchar),3) as t3
from tb) b
where tb.t1=b.t1 and tb.t2=b.t2
'row_number' 不是可以识别的 函数名。
运行中出现这个提示
估计要自己写吧!