22,301
社区成员




CREATE TABLE [eduClassStud](
[Line_ID] [VarChar](10) NOT NULL,
[eduClass_ID] [VarChar](10) NULL,
[Stud_NO] [smallint] NULL,
)
insert into eduClassStud values('SXYQMY0001','QWERTY1000',null)
insert into eduClassStud values('SXYQMY0002','QWERTY1000',null)
insert into eduClassStud values('SXYQMY0003','QWERTY1000',null)
insert into eduClassStud values('SXYQMY0004','QWERTY1000',null)
insert into eduClassStud values('SXYQMY0005','QWERTY1000',null)
insert into eduClassStud values('SXYQMY0006','QWERTY1000',null)
insert into eduClassStud values('SXYQMY0007','QWERTY1000',null)
insert into eduClassStud values('SXYQMY0008','QWERTY1000',null)
insert into eduClassStud values('SXYQMY0009','QWERTY1001',null)
insert into eduClassStud values('SXYQMY0010','QWERTY1001',null)
insert into eduClassStud values('SXYQMY0011','QWERTY1001',null)
insert into eduClassStud values('SXYQMY0012','QWERTY1001',null)
insert into eduClassStud values('SXYQMY0013','QWERTY1001',null)
insert into eduClassStud values('SXYQMY0014','QWERTY1001',null)
insert into eduClassStud values('SXYQMY0015','QWERTY1001',null)
insert into eduClassStud values('SXYQMY0016','QWERTY1001',null)
insert into eduClassStud values('SXYQMY0017','QWERTY1002',null)
insert into eduClassStud values('SXYQMY0018','QWERTY1002',null)
insert into eduClassStud values('SXYQMY0019','QWERTY1002',null)
insert into eduClassStud values('SXYQMY0020','QWERTY1002',null)
insert into eduClassStud values('SXYQMY0021','QWERTY1002',null)
insert into eduClassStud values('SXYQMY0022','QWERTY1002',null)
insert into eduClassStud values('SXYQMY0023','QWERTY1002',null)
insert into eduClassStud values('SXYQMY0024','QWERTY1002',null)
UPDATE [eduClassStud]
SET Stud_NO=SEQ
FROM [eduClassStud] AS A JOIN (
SELECT [Line_ID],SEQ=(SELECT COUNT(*) FROM [eduClassStud] WHERE [eduClass_ID]=T.[eduClass_ID] AND [Line_ID]<=T.[Line_ID])
FROM [eduClassStud] AS T
) B
ON A.[Line_ID]=B.[Line_ID]
SELECT * FROM [eduClassStud]
DROP TABLE [eduClassStud]
/*
Line_ID eduClass_ID Stud_NO
---------- ----------- -------
SXYQMY0001 QWERTY1000 1
SXYQMY0002 QWERTY1000 2
SXYQMY0003 QWERTY1000 3
SXYQMY0004 QWERTY1000 4
SXYQMY0005 QWERTY1000 5
SXYQMY0006 QWERTY1000 6
SXYQMY0007 QWERTY1000 7
SXYQMY0008 QWERTY1000 8
SXYQMY0009 QWERTY1001 1
SXYQMY0010 QWERTY1001 2
SXYQMY0011 QWERTY1001 3
SXYQMY0012 QWERTY1001 4
SXYQMY0013 QWERTY1001 5
SXYQMY0014 QWERTY1001 6
SXYQMY0015 QWERTY1001 7
SXYQMY0016 QWERTY1001 8
SXYQMY0017 QWERTY1002 1
SXYQMY0018 QWERTY1002 2
SXYQMY0019 QWERTY1002 3
SXYQMY0020 QWERTY1002 4
SXYQMY0021 QWERTY1002 5
SXYQMY0022 QWERTY1002 6
SXYQMY0023 QWERTY1002 7
SXYQMY0024 QWERTY1002 8
(24 row(s) affected)
*/
declare @var int
set @var=1
update t set Stud_No= @var,@var=@var+1
update t
set Stud_No=num
from (select eduClass_ID,Stud_No,num=row_number() over(partition by eduClass_ID order by getdate()) from eduClassStud) t
--try:
declare @eduClass_ID int,@Stud_No smallint
select @eduClass_ID=班级id,@Stud_No=0
update eduClassStud set @Stud_No=@Stud_No+1,Stud_No=@Stud_No
where eduClass_ID=@eduClass_ID
declare @eduClassStud table(eduClass_ID int,Stud_No int)
insert into @eduClassStud select 1001,null
union all select 1001,null
union all select 1002,null
union all select 1002,null
union all select 1001,null
union all select 1003,null
union all select 1001,null
union all select 1004,null
union all select 1004,null
union all select 1003,null
union all select 1003,null
union all select 1001,null
union all select 1002,null
union all select 1004,null
declare @i int
update t set @i=isnull(@i,0)+1,Stud_No=@i from @eduClassStud t
update t set t.Stud_No=(select count(1) from @eduClassStud where t.eduClass_ID=eduClass_ID and Stud_No<=t.Stud_No) from @eduClassStud t
select * from @eduClassStud
/*
eduClass_ID Stud_No
----------- -----------
1001 1
1001 2
1002 1
1002 2
1001 3
1003 1
1001 4
1004 1
1004 2
1003 2
1003 3
1001 5
1002 3
1004 3
*/
declare @i int
update eduClassStud set @i=@i+1,Stud_No=@i
update eduClassStud set Stud_No=(select count(1) from eduClassStud t where t.eduClass_ID=eduClass_ID and t.Stud_No<=Stud_No)