有表1 数据如下人员编号 合同编号 签订日期
76 2008-03-3 00:00:00.000
736 2008-03-24 00:00:00.000
823 2008-05-05 00:00:00.000
20 2009-02-28 00:00:00.000
795 2009-03- 00:00:00.000
203 2009-03-8 00:00:00.000
207 2009-03-23 00:00:00.000
2026 2009-04-0 00:00:00.000
2028 2009-04-07 00:00:00.000
2032 2009-04-7 00:00:00.000
204 2009-05-05 00:00:00.000
2047 2009-05-9 00:00:00.000
2055 2009-06-05 00:00:00.000
2068 2009-06-5 00:00:00.000
2070 2009-06-8 00:00:00.000现在要的结果是:我的一个签订日期可能有1条记录,也可能有N条记录。更新合同编号,规则: 签订日期的年月日(8位)+流水号(2位)。
76 2008-03-3 00:00:00.000
736 2008-03-24 00:00:00.000
823 2008-05-05 00:00:00.000
20 2009-02-28 00:00:00.000
795 2009-03- 00:00:00.000
203 2009-03-8 00:00:00.000
207 2009-03-23 00:00:00.000
2026 2009-04-0 00:00:00.000
2028 2009-04-07 00:00:00.000
2032 2009-04-7 00:00:00.000
204 2009-05-05 00:00:00.000
2047 2009-05-9 00:00:00.000
2055 2009-06-05 00:00:00.000
2068 2009-06-5 00:00:00.000
2070 2009-06-8 00:00:00.000现在要的结果是:我的一个签订日期可能有1条记录,也可能有N条记录。更新合同编号,规则: 签订日期的年月日(8位)+流水号(2位)。
CONVERT(VARCHAR(8),[签订日期],112)+
RIGHT('00'+CONVERT(VARCHAR(10),ROW_NUMBER() OVER(PARTITION BY CONVERT(DATETIME,[签订日期],120) ORDER BY [签订日期])),2),*
FROM [表1]
(
select id=row_number over(order by getdate()) from table
)
update table set 合同编号=convert(varchar(8),签订日期,112)+left('00'+convert(varchar(2),id),2)
from tt
(
select id=row_number over(order by getdate()) from table
)
update table set 合同编号=convert(varchar(8),签订日期,112)+right('00'+convert(varchar(2),id),2)
from tt
(
select id=row_number over(order by getdate()) from table
)
update table set 合同编号=convert(varchar(8),签订日期,112)+right('00'+convert(varchar(2),id),2)
from tt
with tt as
(
select *,id=row_number() over (order by getdate()) from table
)
update table set 合同编号=convert(varchar(8),签订日期,112)+left('00'+convert(varchar(2),id),2) from tt
#8就是sql server的语句