已知表Employee DROP TABLE Employee;
CREATE TABLE Employee(
id Int Not null Identity,
name Varchar(255) Null,
mobile Varchar(20) Null,
email Varchar(64) Null,
username Varchar(100) Not null,
password Varchar(100) Not null,
positionId Int Not null,
code Varchar(10) Null,
appurtenantId Int Not null,
status Varchar(10) Not null Default '在职',
acceptMsg Varchar(10) Not null Default '否',
hadLogin int not null default 0,
companyid int not null default 0,
company varchar(50) Null,
inTime Datetime Null,
leaveTime varchar(50) Null,
timeCreated Datetime Not null,
timeLastUpdated Datetime Null,
Primary Key (id)); code 为 "other", 为组 按Id 顺序修改 appurtenantId要求如下:
原表:
id code acceptMsg appurtenantId
1 Other 1 0
2 Other 2 0
3 Other 2 0
4 Other 2 0
5 Other 3 0
6 Other 1 0
7 Other 1 0
8 Other 3 0
9 Sale 1 0 修改后:
id code acceptMsg appurtenantId
1 Other 1 1
6 Other 1 2
7 Other 1 3
2 Other 2 1
3 Other 2 2
4 Other 2 3
5 Other 3 1
8 Other 3 2
9 Sale 1 0
CREATE TABLE Employee(
id Int Not null Identity,
name Varchar(255) Null,
mobile Varchar(20) Null,
email Varchar(64) Null,
username Varchar(100) Not null,
password Varchar(100) Not null,
positionId Int Not null,
code Varchar(10) Null,
appurtenantId Int Not null,
status Varchar(10) Not null Default '在职',
acceptMsg Varchar(10) Not null Default '否',
hadLogin int not null default 0,
companyid int not null default 0,
company varchar(50) Null,
inTime Datetime Null,
leaveTime varchar(50) Null,
timeCreated Datetime Not null,
timeLastUpdated Datetime Null,
Primary Key (id)); code 为 "other", 为组 按Id 顺序修改 appurtenantId要求如下:
原表:
id code acceptMsg appurtenantId
1 Other 1 0
2 Other 2 0
3 Other 2 0
4 Other 2 0
5 Other 3 0
6 Other 1 0
7 Other 1 0
8 Other 3 0
9 Sale 1 0 修改后:
id code acceptMsg appurtenantId
1 Other 1 1
6 Other 1 2
7 Other 1 3
2 Other 2 1
3 Other 2 2
4 Other 2 3
5 Other 3 1
8 Other 3 2
9 Sale 1 0
漏打字
(
id int identity(1,1) primary key,
code varchar(20),
acceptMsg int,
appurtenantId int
)
insert into @TA select 'Other',1,0
insert into @TA select 'Other',2,0
insert into @TA select 'Other',2,0
insert into @TA select 'Other',2,0
insert into @TA select 'Other',3,0
insert into @TA select 'Other',1,0
insert into @TA select 'Other',1,0
insert into @TA select 'Other',3,0
insert into @TA select 'Sale',1,0update @TA set appurtenantId=px from @TA A,
(
select id,code,acceptMsg,(select count(*)+1 from @TA where acceptMsg=A.acceptMsg and id<A.id) px from @TA A where code='Other'
) B where B.id=A.idselect * from @TA
(
id int identity(1,1) primary key,
code varchar(20),
acceptMsg int,
appurtenantId int
)
insert into @TA select 'Other',1,0
insert into @TA select 'Other',2,0
insert into @TA select 'Other',2,0
insert into @TA select 'Other',2,0
insert into @TA select 'Other',3,0
insert into @TA select 'Other',1,0
insert into @TA select 'Other',1,0
insert into @TA select 'Other',3,0
insert into @TA select 'Sale',1,0update @TA set appurtenantId=px from @TA A,
(
select id,acceptMsg,(select count(*)+1 from @TA where acceptMsg=A.acceptMsg and id<A.id) px from @TA A where code='Other'
) B where B.id=A.id
select * from @TA order by acceptMsgid code acceptMsg appurtenantId
----------- -------------------- ----------- -------------
1 Other 1 1
6 Other 1 2
7 Other 1 3
9 Sale 1 0
2 Other 2 1
3 Other 2 2
4 Other 2 3
5 Other 3 1
8 Other 3 2(所影响的行数为 9 行)
from @Employee A
,(select id
,acceptMsg
, row_number() over(partition by acceptMsg order by id) as [order] from @Employee)T
where A.id=T.id And code='Other'