求一个SQL语句

[flyskylf]

ID Name
1 A
2 B
3 C
4 A
5 B
6 C
7 B
8 B
9 C



一共有2个A, 4个B, 3个C
要求查出来的记录为

序号 Name
1 A
2 A
1 B
2 B
3 B
4 B
1 C
2 C
3 C

相同记录进行自动编号,不同的记录重新开始编号.

[chuifengde]

select 序号=(select count(1) from [Table] where Name=a.Name and id<=a.id),
       Name
from [Table] a
order by 2,1

[htl258_Tony]

if object_id('tb') is not null drop table tb 
 go 
create table tb([ID] int,[Name] varchar(10))
insert tb select 1,'A'
union all select 2,'B'
union all select 3,'C'
union all select 4,'A'
union all select 5,'B'
union all select 6,'C'
union all select 7,'B'
union all select 8,'B'
union all select 9,'C'
go
select id=(select count(1) from tb where name=t.name and id<=t.id),name from tb t order by name,id
/*
id          name
----------- ----------
1           A
2           A
1           B
2           B
3           B
4           B
1           C
2           C
3           C

(9 行受影响)
*/

[csdyyr]

SELECT ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ID) AS ID,NAME
FROM (
SELECT ID=1,  NAME='A' UNION ALL 
SELECT 2,  'B' UNION ALL 
SELECT 3,  'C' UNION ALL 
SELECT 4,  'A' UNION ALL 
SELECT 5,  'B' UNION ALL 
SELECT 6,  'C' UNION ALL 
SELECT 7,  'B' UNION ALL 
SELECT 8,  'B' UNION ALL 
SELECT 9,  'C'
) T
/*
ID                   NAME
-------------------- ----
1                    A
2                    A
1                    B
2                    B
3                    B
4                    B
1                    C
2                    C
3                    C
*/

[wzy_love_sly]

declare @tb table(id int,name varchar(50))
insert into @tb select 1,'A'
insert into @tb select 2,'B'
insert into @tb select 3,'C'
insert into @tb select 4,'A'
insert into @tb select 5,'B'
insert into @tb select 6,'C'
insert into @tb select 7,'B'
insert into @tb select 8,'B'
insert into @tb select 9,'C'
--2005
select id=row_number() over(partition by name order by id),name
from @tb
--2000
select id=(select count(*) from @tb where name=t.name and id<=t.id),name
from @tb t
order by name,id

[sdhdy]

select xh=(select count(1) from tb where name=a.name and id<a.id )+1,name from tb a order by name,xh

[htl258_Tony]

SQL2005:

if object_id('tb') is not null drop table tb 
 go 
create table tb([ID] int,[Name] varchar(10))
insert tb select 1,'A'
union all select 2,'B'
union all select 3,'C'
union all select 4,'A'
union all select 5,'B'
union all select 6,'C'
union all select 7,'B'
union all select 8,'B'
union all select 9,'C'
go
select 序号=rank() over(partition by name order by id),name from tb
/*
id          name
----------- ----------
1           A
2           A
1           B
2           B
3           B
4           B
1           C
2           C
3           C

(9 行受影响)
*/

[sdhdy]

if object_id('tb') is not null drop table tb 
 go 
create table tb([ID] int,[Name] varchar(10))
insert tb select 1,'A'
union all select 2,'B'
union all select 3,'C'
union all select 4,'A'
union all select 5,'B'
union all select 6,'C'
union all select 7,'B'
union all select 8,'B'
union all select 9,'C'
go

select xh=(select count(1) from tb where name=a.name and id<a.id )+1,name from tb a order by name,xh

drop table tb
/*
xh          name       
----------- ---------- 
1           A
2           A
1           B
2           B
3           B
4           B
1           C
2           C
3           C

（所影响的行数为 9 行）
*/

[flyskylf]

多谢各位帮忙!

[ljhcy99]

select * from
table
order by name,id

[让你望见影子的墙]

select 序号=row_number() over (partition by name,order by getdate()),name
from tb