create table a1
(id int)
create table a2
(id int, aid int ,title nvarchar(30))
insert into a1
select 1
union all
select 2
union all
select 3
insert into a2
select 2,1,N'aaaa'
union all
select 6,1 ,N'bbbb'
union all
select 7,2, N'cccc'
union all
select 11,2,N'ffff'
union all
select 8,3, N'iiii'
union all
select 9,3, N'pppp'
select * from a1
select * from a2
with tab
as
(select *, rank()over(partition by aid order by id) as sor1 from a2
)
selet aid,id,title,sor1 from tal