update aa set b=((Select count(*) From (select distinct c from #tmp) bb where c>=aa.c )) from #tmp aa
update #tmp set b=(Select count(*) From #tmp a where a.c>#tmp.c)+1
a b c ----------- ----------- -------------------- 5 1 2.20 9 1 2.20 4 2 1.40 7 3 1.30 1 4 1.20 6 5 1.00(所影响的行数为 6 行)
还是有点错,这是最终的了: update #tmp set b=(Select count(distinct c) From #tmp a where a.c>#tmp.c)+1
update a set b=((Select count(*)+1 From #tmp where c>a.c )) from #tmp a
update #tmp set b=isnull((Select count(distinct c) From #tmp a where a.c>#tmp.c),0)+1
update a set b=((Select count(distinct c) From #tmp where c>=a.c )) from #tmp a
CREATE TABLE #tmp ( [a] [int] NULL , [b] [int] NULL , [c] [decimal](18, 2) NULL ) --a:为主键insert into #tmp(a,b,c) select 6, null, 1 union all select 1, null, 1.2 union all Select 7, null, 1.3 union all select 4, null, 1.4 union all select 5, null, 2.2 union all select 9, null, 2.2
update a set b=((Select count(distinct c) From #tmp where c>=a.c )) from #tmp a select * from #tmp order by bdrop table #tmp /*--结果:a b c ----------- ----------- -------------------- 5 1 2.20 9 1 2.20 4 2 1.40 7 3 1.30 1 4 1.20 6 5 1.00(所影响的行数为 6 行)--*/
update aa set b=((Select count(*) From (select distinct c from #tmp) bb where c>=aa.c ))
from #tmp aa
----------- ----------- --------------------
5 1 2.20
9 1 2.20
4 2 1.40
7 3 1.30
1 4 1.20
6 5 1.00(所影响的行数为 6 行)
update #tmp set b=(Select count(distinct c) From #tmp a where a.c>#tmp.c)+1
from #tmp a
from #tmp a
[a] [int] NULL ,
[b] [int] NULL ,
[c] [decimal](18, 2) NULL
)
--a:为主键insert into #tmp(a,b,c)
select 6, null, 1
union all
select 1, null, 1.2
union all
Select 7, null, 1.3
union all
select 4, null, 1.4
union all
select 5, null, 2.2
union all
select 9, null, 2.2
update a set b=((Select count(distinct c) From #tmp where c>=a.c ))
from #tmp a select * from #tmp order by bdrop table #tmp
/*--结果:a b c
----------- ----------- --------------------
5 1 2.20
9 1 2.20
4 2 1.40
7 3 1.30
1 4 1.20
6 5 1.00(所影响的行数为 6 行)--*/