更新排名 ，写法，求高手！该怎么处理

--> 测试数据：[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([列1] varchar(1),[列2] int,[列3] int)
insert [tbl]
select 'A',null,5 union all
select 'A',null,3 union all
select 'B',null,9 union all
select 'B',null,16 union all
select 'B',null,10

;with t
as(
select *,ROW_NUMBER()over(partition by [列1] order by [列3] asc) as id from tbl
)
update [tbl] set [列2]=id from t
where t.列1=tbl.列1 and t.列3=tbl.列3

select * from tbl

/*
列1    列2    列3
A    2    5
A    1    3
B    1    9
B    3    16
B    2    10
*/

------解决方案--------------------

SQL code

--考虑重复排名吗？表应该有主键id吧！

;with ach as
(
  select *,rid=row_number() over (partition by col1 order by col3,id)
  from tb
)

update a
set a.col2 = b.rid
from tb a join ach b on a.id = b.id

--这个写法是05的，2000的如下：

select *,rid=identity(int,1,1) into #tb
from tb
order by col1,col3,id

update a
set a.col2 = (select count(*) from #tb where col1=a.col1 and rid<=a.rid)
from tb a

------解决方案--------------------

探讨

mssql 2000 数据库

------解决方案--------------------

SQL code

use tempdb
go
if object_id('tempdb..#') Is not null
    Drop Table #
create table #(col1 nvarchar(50),col2 int,col3 int)
insert into #   ( col1,  col3 )
    Select 'A',5 Union All
    Select 'A',3 Union All
    Select 'B',9 Union All 
    Select 'B',16 Union All 
    Select 'B',10

Update a 
    Set a.col2=b.seq
    From # As a
        Inner Join (
        Select x.col1,x.col3,count(1) As seq
            from # As x
                left Outer Join # As y On y.col1=x.col1    And y.col3<=x.col3
                Group By x.col1,x.col3
        ) As b On b.col1=a.col1
            And b.col3=a.col3

Select * from #

------解决方案--------------------

探讨

#5楼



你的写法比较方便，但如果 同样的数字是同一排名。


是否可以按 一个时间字段 区分排名？


求写法写法？



col1 col2 col3 col4
-----------------------------
A ? 5
A ? 3
B ? 9
B ? 16
B ? 10 2012-04-09 02：00：00
B ? 10 2012……