Sql问题。

[jamfchan]

T表：
id name
1 a
2 a
3 b
4 b
5 b
怎样才能输出以下
1 a-1
2 a-2
3 b-1
4 b-2
5 b-3

谢谢。

[九斤半]

CREATE TABLE T(ID INT IDENTITY(1,1),NAME NVARCHAR(10))
INSERT T(NAME) SELECT 'a'
INSERT T(NAME) SELECT 'a'
INSERT T(NAME) SELECT 'b'
INSERT T(NAME) SELECT 'b'
INSERT T(NAME) SELECT 'b'
SELECT * FROM T

SELECT
*,
NAME2=NAME+'-'+(SELECT RTRIM(COUNT(*)) FROM T WHERE NAME=I.NAME AND ID<=I.ID)
FROM T I

DROP TABLE T

[九斤半]

ID NAME NAME2
----------- ---------- -----------------------
1 a a-1
2 a a-2
3 b b-1
4 b b-2
5 b b-3

[zlp321002]

declare @t table(id int, name varchar(10))
insert into @t select 1,'a'
union all select 2,'a'
union all select 3,'b'
union all select 4,'b'
union all select 5,'b'

select *,name=name+'-'+ltrim((select sum(1) from @t where name=tb.name and id<=tb.id)) from @t tb
/*
id name name
----------- ---------- -----------------------
1 a a-1
2 a a-2
3 b b-1
4 b b-2
5 b b-3

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

[jamfchan]

谢谢