22,302
社区成员




;with t as(
select new_no='MK-'
+convert(varchar(6),voh_dd,112)
+'-'+ltrim(b.[ITM])
+'-'+right('0000'+ltrim
(
row_number()
over(partition by convert(varchar(6),voh_dd,112),a.[voh_flag] order by voh_dd)
),4)
,[voh_no] ,[sq_no],[voh_dd], [voh_flag]
from [ta] a,[tb] b where a.[voh_flag]=b.[VOG_FLAG]
)
update t set [voh_no]=new_no
update
a
set
voh_no='MK-'
+convert(varchar(6),voh_dd,112)
+'-'+ltrim(b.[ITM])
+'-'+right('0000'+ltrim
(
row_number()
over(partition by convert(varchar(6),voh_dd,112),a.[voh_flag] order by voh_dd)
),4)
from [ta] a,[tb] b
where a.[voh_flag]=b.[VOG_FLAG]
select new_no='MK-'+convert(varchar(6),voh_dd,112)+'-'+ltrim(b.[ITM])+'-'+right('0000'+ltrim(row_number() over(partition by convert(varchar(6),voh_dd,112),a.[voh_flag] order by voh_dd)),4)
,[voh_no] ,[sq_no],[voh_dd], [voh_flag]
from [ta] a,[tb] b where a.[voh_flag]=b.[VOG_FLAG]
/*
new_no voh_no sq_no voh_dd voh_flag
------------------------------- ------------------- ----- ----------------------- --------
MK-201202-1-0001 MK-20120204-1-00001 00001 2012-02-04 14:16:06.520 现
MK-201202-1-0002 MK-20120204-1-00002 00002 2012-02-04 14:19:42.443 现
MK-201202-1-0003 MK-20120206-1-00001 00001 2012-02-06 09:23:37.207 现
MK-201202-1-0004 MK-20120206-1-00002 00002 2012-02-06 09:32:40.907 现
MK-201202-1-0005 MK-20120206-1-00003 00003 2012-02-06 09:35:36.970 现
MK-201202-1-0006 MK-20120206-1-00004 00004 2012-02-06 10:34:51.530 现
MK-201202-3-0001 MK-20120204-3-00001 00001 2012-02-04 14:17:38.493 转
MK-201202-3-0002 MK-201202-3-0001 0001 2012-02-13 13:54:10.987 转
MK-201202-3-0003 MK-201202-3-0004 -0002 2012-02-13 14:19:50.327 转
MK-201202-3-0004 MK-201202-3-0006 -0003 2012-02-13 14:19:50.387 转
MK-201202-3-0005 MK-201202-3-0004 -0004 2012-02-13 14:19:50.433 转
MK-201202-3-0006 MK-201202-3-0005 -0005 2012-02-13 14:19:50.507 转
MK-201202-3-0007 MK-201202-3-0006 -0006 2012-02-13 14:19:50.560 转
MK-201202-3-0008 MK-201202-3-0007 -0007 2012-02-13 14:22:31.780 转
MK-201202-3-0009 MK-201202-3-0008 -0008 2012-02-13 14:22:43.070 转
MK-201202-3-0010 MK-201202-3-0009 -0009 2012-02-13 14:27:30.250 转
(16 行受影响)
*/
select new_no='MK-'+convert(varchar(6),voh_dd,112)+'-'+ltrim(b.[ITM])+'-'+right('0000'+row_number() over(partition by convert(varchar(6),voh_dd,112) order by voh_dd),4)
from [ta] a,[tb] b where a.[voh_flag]=b.[VOG_FLAG]
/*
new_no
-------------------------------
MK-201202-1-1
MK-201202-3-2
MK-201202-1-3
MK-201202-1-4
MK-201202-1-5
MK-201202-1-6
MK-201202-1-7
MK-201202-3-8
MK-201202-3-9
MK-201202-3-10
MK-201202-3-11
MK-201202-3-12
MK-201202-3-13
MK-201202-3-14
MK-201202-3-15
MK-201202-3-16
(16 行受影响)
*/