向各位高手求这样的Sql语句

[KETTLY800730]

e_id m_id name
bg xx huihui
bg dd xiaoxiao
bg cc mingming
xc ss xixi


e_id m_id name number
bg xx huihui 1
bg dd xiaoxiao 2
bg cc mingming 3
xc ss xixi 1
向各位高手求这样的Sql语句

[中国风]

生成临时表：
select *,number=0 into # from T order by e_ID
go
declare @e_id nvarchar(2),@i int
update #
set @i=case when e_id=@e_id then @i+1 else 1 end ,number=@i,@e_id=e_id
go
select * from #

[中国风]

2005;
select *,number=row_number()over(partition by e_id order by e_id) from T

[areswang]

LZ要什么语句？

[中国风]

create table T(e_id nvarchar(2) ,             m_id  nvarchar(2) ,         name nvarchar(10))
insert T select 'bg',                   'xx',             'huihui' 
insert T select 'bg',                   'dd',             'xiaoxiao' 
insert T select 'bg',                   'cc',             'mingming' 
insert T select 'xc',                   'ss',             'xixi'
go

select   *,number=0   into   #   from   T   order   by   e_ID 
go 
declare   @e_id   nvarchar(2),@i   int 
update   # 
set   @i=case   when   e_id=@e_id   then   @i+1   else   1   end   ,number=@i,@e_id=e_id 
go 
select   *   from   #

e_id m_id name       number      
---- ---- ---------- ----------- 
bg   xx   huihui     1
bg   dd   xiaoxiao   2
bg   cc   mingming   3
xc   ss   xixi       1

（所影响的行数为 4 行）

[-狙击手-]

--如果m_id是唯 一的，还可以这样

create table T(e_id nvarchar(2) ,             m_id  nvarchar(2) ,         name nvarchar(10))
insert T select 'bg',                   'xx',             'huihui' 
insert T select 'bg',                   'dd',             'xiaoxiao' 
insert T select 'bg',                   'cc',             'mingming' 
insert T select 'xc',                   'ss',             'xixi'
go

---
select * ,[number] = (select count(1)+ 1 from T where e_id = tt.e_id and m_id > tt.m_id)
from T tt

drop table t

/*

e_id m_id name       number      
---- ---- ---------- ----------- 
bg   xx   huihui     1
bg   dd   xiaoxiao   2
bg   cc   mingming   3
xc   ss   xixi       1

（所影响的行数为 4 行）

*/