34,575
社区成员
发帖
与我相关
我的任务
分享
create table #a(a varchar(50),b varchar(10))
insert into #a
select 'aaa','' union all
select 'aaa','' union all
select 'aaa','' union all
select 'aab','' union all
select 'aac','' union all
select 'aac',''
select * from #a
我要得到数据
aaa 01
aaa 02
aaa 03
aab 01
aac 01
aac 02
create table #a(a varchar(50),b varchar(10))
insert into #a
select 'aaa','' union all
select 'aaa','' union all
select 'aaa','' union all
select 'aab','' union all
select 'aac','' union all
select 'aac',''
select t.* , id = identity(int,1,1) into tmp from #a t
update tmp set b = right('0' + cast((select count(1) from tmp where a = t.a and id < t.id) + 1 as varchar),2) from tmp t
select * from tmp
drop table #a,tmp
/*
a b id
-------------------------------------------------- ---------- -----------
aaa 01 1
aaa 02 2
aaa 03 3
aab 01 4
aac 01 5
aac 02 6
(所影响的行数为 6 行)
*/
create table abc(a varchar(50),b varchar(10))
insert into abc
select 'aaa','' union all
select 'aaa','' union all
select 'aaa','' union all
select 'aab','' union all
select 'aac','' union all
select 'aac',''
CREATE TABLE #Test(a varchar(50),num int)
declare @a varchar(50)
DECLARE @i int
DECLARE hr_cursor CURSOR FOR
SELECT DISTINCT a,COUNT(*) as c from abc group by a
OPEN hr_cursor
FETCH NEXT FROM hr_cursor
INTO @a,@i
WHILE @@FETCH_STATUS = 0
BEGIN
declare @count int
set @count=1
while @count<=@i
begin
insert into #Test values(@a,@count)
set @count=@count+1
end
FETCH NEXT FROM hr_cursor
INTO @a,@i
END
SELECT * FROM #Test
close hr_cursor
deallocate hr_cursor
create table #a(a varchar(50),b varchar(10))
insert into #a
select 'aaa','' union all
select 'aaa','' union all
select 'aaa','' union all
select 'aab','' union all
select 'aac','' union all
select 'aac',''
select a,
row_number()over(partition by a order by getdate()) as num
from #a
a num
aaa 1
aaa 2
aaa 3
aab 1
aac 1
aac 2