select
序号=(select sum(1) from table1 where 部门=A.部门 and 姓名<=A.姓名),
部门,
姓名
from
table1 A
order by
部门,
(select sum(1) from table1 where 部门=A.部门 and 姓名<=A.姓名)
select IDENTITY(int, 1,1) AS id, 部门, 姓名 into #t1 from table1 order by 部门, 姓名
select a.id-b.id+1 as 序号, a.部门, a.姓名 into table2 from #t1 a inner join (select min(id) id ,部门 from #t1 group by 部门) b on a.部门=b.部门
insert into table1 select '技术部','小李'
insert into table1 select '技术部','小王'
insert into table1 select '技术部','小张'
insert into table1 select '市场部','小明'
insert into table1 select '市场部','小军'
insert into table1 select '市场部','小红'
--要成的是table2
select IDENTITY(int, 1,1) AS id, 部门, 姓名 into #t1 from table1 order by 部门, 姓名
select a.id-b.id+1 as 序号, a.部门, a.姓名 into table2 from #t1 inner join (select min(id) id ,部门 from #t1 group by 部门) b on a.部门=b.部门
select * from table2
drop table table1
drop table table2
drop table #t1
select IDENTITY(int, 1,1) AS id, 部门, 姓名 into #t1 from table1 order by 部门, 姓名
select a.id-b.id+1 as 序号, a.部门, a.姓名 into 要生成的表 from #t1 inner join (select min(id) id ,部门 from #t1 group by 部门) b on a.部门=b.部门
select IDENTITY(int, 1,1) AS id, 部门, 姓名 into #t1 from table1 order by 部门, 姓名
select a.id-b.id+1 as 序号, a.部门, a.姓名 into 要生成的表 from #t1 a inner join (select min(id),部门 from #t1 group by 部门) b on a.部门=b.部门
select IDENTITY(int, 1,1) AS id, 部门, 姓名 into #t1 from table1 order by 部门, 姓名
select a.id-b.id+1 as 序号, a.部门, a.姓名 into 要生成的表 from #t1 inner join (select min(id),部门 from #t1 group by 部门) b on a.部门=b.部门