請教一個重排序問題?

[kisscong]

現在有表 A
Num Shop Type
a Z
b Z
c Z
d ZZ
f ZZ
g ZZZ
h ZZZ
現在要求根據不同Type的店鋪為他們排列, 不同Type都從1開始, 結果應該是
Num Shop Type
1 a Z
2 b Z
3 c Z
1 d ZZ
2 f ZZ
1 g ZZZ
2 h ZZZ

[OracleRoob]

select 
    (select count(*) from 表A where Type=t.Type and Shop<=t.Shop) as Num, 
    Shop,
    Type
from 表A as t
order by type, Shop

[fa_ge]

select num=(select count(1) from t where type=a.type and shop<=a.shop),
shop,type
from t a

[fa_ge]

慢了,

[中国风]

SELECT
(SELECT COUNT(1) FROM TABLE1 WHERE TYPE=T.TYPE AND SHOP!>T.SHOP) AS NUM
,*
FROM TABLE1 T

[dawugui]

create table tb(Shop varchar(10),Type varchar(10))
insert into tb values('a',          'Z' )
insert into tb values('b',          'Z' )
insert into tb values('c',          'Z' )
insert into tb values('d',          'ZZ' ) 
insert into tb values('f',          'ZZ' )
insert into tb values('g',          'ZZZ' )
insert into tb values('h',          'ZZZ' )
go

select num=(select count(1) from tb where type=a.type and shop<a.shop)+1 , * from tb a order by type , num

drop table tb

/*
num         Shop       Type       
----------- ---------- ---------- 
1           a          Z
2           b          Z
3           c          Z
1           d          ZZ
2           f          ZZ
1           g          ZZZ
2           h          ZZZ

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