22,210
社区成员
发帖
与我相关
我的任务
分享
--2005
select *,row_number() over (order by name) as TT from table1
declare @t table(name varchar(5), id varchar(5), age int , sex varchar(2) )
insert @t select 'A', '045', 18, 'M'
insert @t select 'B' , '043', 14, 'w'
insert @t select 'C' , '041', 15, 'M'
insert @t select 'D', '009', 16 , 'w'
insert @t select 'E', '067', 18, 'M'
insert @t select 'F', ' 055', 19, 'w'
select * ,(select count(*)+1 from @t t1 where t1.Name <t2.Name )as TT from @t t2
/*
name id age sex TT
----- ----- ----------- ---- -----------
A 045 18 M 1
B 043 14 w 2
C 041 15 M 3
D 009 16 w 4
E 067 18 M 5
F 055 19 w 6
(影響 6 個資料列)
*/
--测试资料
CREATE TABLE tb(
ID1 char(2) NOT NULL,
ID2 char(4) NOT NULL,
col int,
PRIMARY KEY(ID1,ID2))
INSERT tb SELECT 'aa','0001',1
UNION ALL SELECT 'aa','0003',2
UNION ALL SELECT 'aa','0004',3
UNION ALL SELECT 'bb','0005',4
UNION ALL SELECT 'bb','0006',5
UNION ALL SELECT 'cc','0007',6
UNION ALL SELECT 'cc','0009',7
GO
--重排编号处理
DECLARE @ID1 char(2),@ID2 int
UPDATE a SET ID2=RIGHT(10000
+(SELECT COUNT(*) FROM tb WHERE ID1=a.ID1 AND ID2<=a.ID2)
,4)
FROM tb a
SELECT * FROM tb
/*--结果
ID1 ID2 col
---- ---- -----------
aa 0001 1
aa 0002 2
aa 0003 3
bb 0001 4
bb 0002 5
cc 0001 6
cc 0002 7
--*/
ALTER TABLE tb_name
ADD TT INT IDENTITY(1,1)