sql 2000分组问题？

[cgl7822]

表A有100个数据

求每5个记录分组，就是5个记录为一个组

表A
a1 a2
--------------------
1 a
2 b
3 c
4 d
5 e
6 4
7 f
8 ac
9 g
10 m
11 aa
..
添加一个虚拟新字段 a3 查询结果如下 没五个记录添加 1，2，3 ......分组生成 虚拟新字段 a3

a1 a2 a3
--------------------
1 a 1
2 b 1
3 c 1
4 d 1
5 e 1
6 4 2
7 f 2
8 ac 2
9 g 2
10 m 2
11 aa 3
12 bb 3
13 cc 3
14 dd 3
15 gg 3
16 rr 4
...
...

[卖水果的net]

-- 参考一下这个写法，效率可能会差点。。
select top 16  id , cast(name  as varchar(10)) name into test 
from sysobjects 
order by id 
go
select id , name , (select COUNT(*) from test x where test.id > id ) / 5 + 1 as newcol
 from test 
go
drop table test 
go


(16 行受影响)
id          name       newcol
----------- ---------- -----------
3           sysrscols  1
5           sysrowsets 1
7           sysallocun 1
8           sysfiles1  1
17          syspriorit 1
19          sysfgfrag  2
23          sysphfg    2
24          sysprufile 2
25          sysftinds  2
27          sysowners  2
29          sysprivs   3
34          sysschobjs 3
41          syscolpars 3
44          sysnsobjs  3
46          syscerts   3
49          sysxprops  4

(16 行受影响)

[习惯性蹭分]

如果表A中A1是從1 開始的自增長序列可以以下語句：

select a1,a2,ceiling(a1/5.) as rn
from [表A]

[kidbuu]

select a1,a2,(rn+4)/5 as a3 from (select row_number() over (order by (select 1)) as rn,a1,a2 from A) as t

[cgl7822]

大哥我的是SQL2000呀

[shadowpj]

搞定了兄弟！分给我把 select ((select count(1) from A t2 where t2.a1<=t1.a1)-1)/5+1 as a3, ((select count(1) from A t2 where t2.a1<=t1.a1)-1)%5+1 as a4, a1,a2 from A t1