求一SQL，尽可能不用存储过程或其它
表A
id subject
1 aa
1 bb
1 cc
1 dd
2 aa
2 bb
2 cc
要得到如下结果
1 aa 1
1 bb 2
1 cc 3
1 dd 4
2 aa 1
2 bb 2
2 cc 3
[解决办法]
select *, [count]=(SELECT COUNT(1)
FROM [表A] b
WHERE b.subject = a.subject)
FROM [表A] a
[解决办法]
参考下面的语句：
select id,subject,
(select count(*)+1 from 表A d where d.id = t.id and d.subject < t.subject) 别名
from 表A t
其中subject支持数值及字符串默认的排序规则
[解决办法]
LZ 表中一定要是 order by ID,subject排序 才能用这个
select id,subject,
(select count(*)+1 from 表A d where d.id = t.id and d.subject < t.subject) 别名
from 表A t
[解决办法]
写得不规范.但功能实现了.
create table tb_a
(
myid int identity(1,1) primary key,
val varchar(20) not null
)
select tba.*,(select min(a.myid) from tb_a a where tba.val = a.val) from tb_a tba
[解决办法]
select * ,ord=(select count(*)+1 from T WHERE a.subject> subject and a.id=id) from T a
