求一个SQL查询语句，谢谢。

[944136]

现有一个表的字段如下：
A1 A2 A3 A4 A5
001 2006-10-11 2 A41 A51
001 2006-10-11 2 A42 A52
002 2006-10-09 3 A43 A53
003 2006-10-10 5 A44 A54
002 2006-10-11 6 A45 A55
001 2006-10-10 7 A46 A56

要求通过查询输出如下记录:
A1 A2 A3 A4 A5 SEQ_NO
001 2006-10-10 7 A46 A56 1
001 2006-10-11 2 A41 A51 1
001 2006-10-11 2 A42 A52 2
002 2006-10-09 3 A43 A53 1
002 2006-10-11 6 A45 A55 1
003 2006-10-10 5 A44 A54 1

即：增加一个顺序号，A2中为同一天记录时，按顺序增加一个顺序号。

谢谢。

[crazyflower]

declare @cc int
declare @A1 nvarchar(10)
declare @A2 datetime
declare @A3 int
declare @A4 nvarchar(10)
declare @A4 nvarchar(10)
update 表名 set SEQ_NO=1
declare cursor_1 cursor for select A1,A2,A3,A4,A5 from 表名
open cursor_1
fetch next from cursor_1 into @A1,@A2,@A3,@A4,@A5
while @@fetch_status=0
begin
select @cc=(select max(SEQ_NO) from 表名 where A2=@date)+1
update 表名 set SEQ_NO=@cc where A1=@A1 and A2=@A2 and A3=@A3 and A4=@A4 and A5=@A5
fetch next from cursor_1 into @A1,@A2,@A3,@A4,@A5
end

[crazyflower]

不好意思，当中写错一点
declare @cc int
declare @A1 nvarchar(10)
declare @A2 datetime
declare @A3 int
declare @A4 nvarchar(10)
declare @A5 nvarchar(10)
update 表名 set SEQ_NO=1
declare cursor_1 cursor for select A1,A2,A3,A4,A5 from 表名
open cursor_1
fetch next from cursor_1 into @A1,@A2,@A3,@A4,@A5
while @@fetch_status=0
begin
select @cc=(select max(SEQ_NO) from 表名 where A2=@A2)+1
update 表名 set SEQ_NO=@cc where A1=@A1 and A2=@A2 and A3=@A3 and A4=@A4 and A5=@A5
fetch next from cursor_1 into @A1,@A2,@A3,@A4,@A5
end

[xiao_deng]

select *,(select count(*) as SEQ_NO from totest where A1=t.A1 and A2=t.A2) from totest as t order by id desc

[chuifengde]

declare @a table(A1 varchar(10), A2 smalldatetime, A3 int, A4 varchar(10), A5 varchar(10))
insert @a select '001', '2006-10-11',2, 'A41', 'A51'
union all select '001', '2006-10-11', 2 ,'A42', 'A52'
union all select '002', '2006-10-09', 3 ,'A43', 'A53'
union all select '003', '2006-10-10', 5 ,'A44', 'A54'
union all select '002', '2006-10-11', 6 ,'A45', 'A55'
union all select '001', '2006-10-10', 7 ,'A46', 'A56'

select a1,a2,a3,a4,a5,id=(select count(1) from @a where a1=a.a1 and a2=a.a2 and a5<=a.a5) from @a a order by a1,a2

[xiao_deng]

select *,(select count(*) as SEQ_NO from totest where A1=t.A1 and A2=t.A2) from totest as t order by A2 desc

[冷箫轻笛]

同一天中A4是否可以重复？如果不可以重复，则：

select *,SEQ_NO = (select count(1) from totest where A2=A.A2 and A4 <= A.A4)
from totest A

[早起晚睡]

来晚了

[早起晚睡]

chuifengde(树上的鸟儿) 经过验证

[冷箫轻笛]

如果其他的字段都可能有重复,则:

select A1,A2,A3,A4,A5,
SEQ_NO =(select counst(1) from totest where a2 = a.a2 and checksum(*) <= a.chksum)
from
(select *,checksum(*) as chksum from totest) a
order by a2,SEQ_NO

[944136]

如果A4/A5列都有可能重复，怎么处理？

coolingpipe(冷箫轻笛)兄的好像结果不对，SEQ_NO结果不对。


chuifengde(树上的鸟儿)兄的A5不同的结果没有问题，如果相同，就只有最大值了。

谢谢大家的帮忙。

[944136]

使用coolingpipe(冷箫轻笛)的结果已经解决。

在where 条件中增加A1字段。结贴。

select A1,A2,A3,A4,A5,
SEQ_NO =(select counst(1) from totest where a2 = a.a2 and a1=a.a1 and checksum(*) <= a.chksum)
from
(select *,checksum(*) as chksum from totest) a
order by a2,SEQ_NO