declare @table table (MainID int,Name varchar(5)) insert into @table select 1,'jack' union all select 1,'jim' union all select 1,'frank' union all select 2,'tom' union all select 2,'rose' union all select 3,'lucy' union all select 3,'lily'select MainID,row_number() over (partition by MainID order by MainID) as ID,Name from @table /* MainID ID Name ----------- -------------------- ----- 1 1 jack 1 2 jim 1 3 frank 2 1 tom 2 2 rose 3 1 lucy 3 2 lily */
本帖最后由 roy_88 于 2011-05-12 18:42:27 编辑
Create table #T(MainID int,Name varchar(30)) Insert into #T(MainID,name) Select 1,'jack' union all Select 1,'jim' union all Select 1,'frank' union all Select 2,'tom' union all Select 2,'rose' union all Select 3,'lucy' union all Select 3,'lily'Select MainID,row_number()over(partition by MainID order by name) as ID,Name From #T
create table #tb(MainID int,Name varchar(5)) insert into #tb select 1,'jack' union all select 1,'jim' union all select 1,'frank' union all select 2,'tom' union all select 2,'rose' union all select 3,'lucy' union all select 3,'lily'select MainID,row_number() over (partition by MainID order by MainID) as ID,Name from #tb /* MainID ID Name ----------- -------------------- ----- 1 1 jack 1 2 jim 1 3 frank 2 1 tom 2 2 rose 3 1 lucy 3 2 lily */
declare @table table (MainID int,Name varchar(5))
insert into @table
select 1,'jack' union all
select 1,'jim' union all
select 1,'frank' union all
select 2,'tom' union all
select 2,'rose' union all
select 3,'lucy' union all
select 3,'lily'select MainID,row_number()
over (partition by MainID order by MainID) as ID,Name from @table
/*
MainID ID Name
----------- -------------------- -----
1 1 jack
1 2 jim
1 3 frank
2 1 tom
2 2 rose
3 1 lucy
3 2 lily
*/
Insert into #T(MainID,name)
Select 1,'jack' union all
Select 1,'jim' union all
Select 1,'frank' union all
Select 2,'tom' union all
Select 2,'rose' union all
Select 3,'lucy' union all
Select 3,'lily'Select MainID,row_number()over(partition by MainID order by name) as ID,Name
From #T
create table #tb(MainID int,Name varchar(5))
insert into #tb
select 1,'jack' union all
select 1,'jim' union all
select 1,'frank' union all
select 2,'tom' union all
select 2,'rose' union all
select 3,'lucy' union all
select 3,'lily'select MainID,row_number()
over (partition by MainID order by MainID) as ID,Name from #tb
/*
MainID ID Name
----------- -------------------- -----
1 1 jack
1 2 jim
1 3 frank
2 1 tom
2 2 rose
3 1 lucy
3 2 lily
*/