已知:
日期 凭证号 类别
2014-01-31 003 01
2014-01-31 003 01
2014-01-31 005 01
2014-01-31 005 01
2014-01-31 005 02
2014-01-31 005 02
2014-01-31 002 03
2014-01-31 002 03
2014-02-27 006 01
2014-02-27 006 01
2014-02-27 006 01
2014-02-27 004 02
2014-02-27 004 02
2014-02-27 008 02
2014-02-27 008 02
2014-02-27 009 02
2014-02-27 009 02得到:根据日期、类别对凭证号按顺序重新赋值。如2014-01-31对类别为01的2条凭证号由003变成001、005变成002等。
日期 凭证号 类别
2014-01-31 001 01
2014-01-31 001 01
2014-01-31 002 01
2014-01-31 002 01
2014-01-31 001 02
2014-01-31 001 02
2014-01-31 001 03
2014-01-31 001 03
2014-02-27 001 01
2014-02-27 001 01
2014-02-27 001 01
2014-02-27 001 02
2014-02-27 001 02
2014-02-27 002 02
2014-02-27 002 02
2014-02-27 003 02
2014-02-27 003 02
日期 凭证号 类别
2014-01-31 003 01
2014-01-31 003 01
2014-01-31 005 01
2014-01-31 005 01
2014-01-31 005 02
2014-01-31 005 02
2014-01-31 002 03
2014-01-31 002 03
2014-02-27 006 01
2014-02-27 006 01
2014-02-27 006 01
2014-02-27 004 02
2014-02-27 004 02
2014-02-27 008 02
2014-02-27 008 02
2014-02-27 009 02
2014-02-27 009 02得到:根据日期、类别对凭证号按顺序重新赋值。如2014-01-31对类别为01的2条凭证号由003变成001、005变成002等。
日期 凭证号 类别
2014-01-31 001 01
2014-01-31 001 01
2014-01-31 002 01
2014-01-31 002 01
2014-01-31 001 02
2014-01-31 001 02
2014-01-31 001 03
2014-01-31 001 03
2014-02-27 001 01
2014-02-27 001 01
2014-02-27 001 01
2014-02-27 001 02
2014-02-27 001 02
2014-02-27 002 02
2014-02-27 002 02
2014-02-27 003 02
2014-02-27 003 02
update tablename set 凭证号='002' where 日期='2014-01-31' and 类别='01' and 凭证号='005'
update tablename set 凭证号='001' where 日期='2014-01-31' and 类别='02' and 凭证号='005'
update tablename set 凭证号='001' where 日期='2014-01-31' and 类别='03' and 凭证号='002'
update tablename set 凭证号='001' where 日期='2014-02-27' and 类别='01' and 凭证号='006'
update tablename set 凭证号='001' where 日期='2014-02-27' and 类别='02' and 凭证号='004'
update tablename set 凭证号='002' where 日期='2014-02-27' and 类别='02' and 凭证号='008'
update tablename set 凭证号='003' where 日期='2014-02-27' and 类别='02' and 凭证号='009'
--> 测试数据:#tab
if object_id('tempdb.dbo.#tab') is not null drop table #tab
go
create table #tab([日期] datetime,[凭证号] varchar(3),[类别] varchar(2))
insert #tab
select '2014-01-31','003','01' union all
select '2014-01-31','003','01' union all
select '2014-01-31','005','01' union all
select '2014-01-31','005','01' union all
select '2014-01-31','005','02' union all
select '2014-01-31','005','02' union all
select '2014-01-31','002','03' union all
select '2014-01-31','002','03' union all
select '2014-02-27','006','01' union all
select '2014-02-27','006','01' union all
select '2014-02-27','006','01' union all
select '2014-02-27','004','02' union all
select '2014-02-27','004','02' union all
select '2014-02-27','008','02' union all
select '2014-02-27','008','02' union all
select '2014-02-27','009','02' union all
select '2014-02-27','009','02'
SELECT a.[日期],RIGHT('000'+RTRIM(c.rn),3) [凭证号],a.[类别] FROM #tab a LEFT JOIN
(SELECT *,ROW_NUMBER() OVER(PARTITION BY [日期],[类别] ORDER BY [日期],[类别]) rn
FROM (SELECT DISTINCT * FROM #tab)b ) c
ON a.[日期]=c.[日期] AND a.[凭证号] = c.[凭证号] AND a.[类别] = c.[类别]
,('2014-01-31','003','01')
,('2014-01-31','005','01')
,('2014-01-31','005','01')
,('2014-01-31','005','02')
,('2014-01-31','005','02')
,('2014-01-31','002','03')
,('2014-01-31','002','03')
,('2014-02-27','006','01')
,('2014-02-27','006','01')
,('2014-02-27','006','01')
,('2014-02-27','004','02')
,('2014-02-27','004','02')
,('2014-02-27','008','02')
,('2014-02-27','008','02')
,('2014-02-27','009','02')
,('2014-02-27','009','02')
select b.*,'00' + CONVERT(nvarchar,a.nid) as nid from (
select *,ROW_NUMBER() over (partition by a.dt,a.tp order by a.sn) as nid from (
select dt,sn,tp from @tb group by dt,tp,sn
) a
) a right join @tb b on a.dt=b.dt where a.sn=b.sn and a.tp=b.tp order by id
(
Date DATETIME,
CerID VARCHAR(16),
TypeID VARCHAR(16)
)
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-01-31' , '003' , '01')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-01-31' , '003' , '01')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-01-31' , '005' , '01')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-01-31' , '005' , '01')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-01-31' , '005' , '02')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-01-31' , '005' , '02')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-01-31' , '002' , '03')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-01-31' , '002' , '03')INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '006' , '01')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '006' , '01')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '006' , '01')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '004' , '02')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '004' , '02')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '008' , '02')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '008' , '02')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '009' , '02')
INSERT INTO Test(Date,CerID,TypeID) VALUES('2014-02-27' , '009' , '02')SELECT Date, CerID ,TypeID INTO #temp FROM Test GROUP BY Date,CerID,TypeID ORDER BY Date,TypeID,CerID--更新
UPDATE test
SET test.CerID = T.NewVal
FROM test INNER JOIN
(
SELECT date,CerID,TypeID, '00' + CONVERT(VARCHAR(16),ROW_NUMBER() OVER(PARTITION BY Date,TypeID ORDER BY CerID)) 'NewVal' FROM #temp
) t ON test.Date = t.Date AND test.CerID = t.CerID AND test.TypeID = t.TypeID