select rank() over (order by 被排序的列名) as 名次,* into 一个表 from 被排序的原表
select *,rank()over(order by col) into newtb from tb
如果原表已存在:insert newtb select *,rank()over(order by col) from tb--指定列名的保存:insert newtb(coll1...,rnk) select col1...,rank()over(order by col) from tb
update a set a.排名=b.rk from tb a join (select *,rk=rank() over(order by 值 desc) from tb) b where a.id=b.id --这里的id是指表的主键楼主最好给出你的表结构,测试数据,算法和你想要的结果
select rank() over (order by A) as B into 结果表 from 原表
以上是由小到大排,如果想由大到小,如下 select rank() over (order by A desc) as B into 结果表 from 原表
这个就是啊 select rank() over (order by A) as B into 结果表 from 原表
如果你不想生成另一个结果表 就这样 select rank() over (order by A) as B from 原表
这不早说,更好办:update t set a=rn from ( select *,rn=rank()over(order by col) from tb ) as t
忘了加上其它列,加上了如下: select rank() over (order by A),* as B from 原表
晕,我上面写错了,如下: select rank() over (order by A) as B,* from 原表
--测试环境 if OBJECT_ID('tb') is not null drop table tb; go create table tb ( id int identity, code varchar(10), name varchar(20), B int ); go insert into tb select '001','AAA' ,null union all select '001','AAA' ,null union all select '001','AAA' ,null union all select '002','BBB' ,null union all select '003','CCC' ,null union all select '002','BBB' ,null go --查询 update t set B=rk from (select B,rk=rank() over(order by name ) from tb)tselect * from tb order by b --结果 /*(6 行受影响)(6 行受影响) id code name B ----------- ---------- -------------------- ----------- 1 001 AAA 1 2 001 AAA 1 3 001 AAA 1 4 002 BBB 4 6 002 BBB 4 5 003 CCC 6(6 行受影响) */
set a.排名=b.rk
from tb a
join (select *,rk=rank() over(order by 值 desc) from tb) b
where a.id=b.id --这里的id是指表的主键楼主最好给出你的表结构,测试数据,算法和你想要的结果
select rank() over (order by A desc) as B into 结果表 from 原表
select rank() over (order by A) as B into 结果表 from 原表
就这样
select rank() over (order by A) as B from 原表
from (
select *,rn=rank()over(order by col)
from tb
) as t
select rank() over (order by A),* as B from 原表
select rank() over (order by A) as B,* from 原表
if OBJECT_ID('tb') is not null drop table tb;
go
create table tb
(
id int identity,
code varchar(10),
name varchar(20),
B int
);
go
insert into tb
select '001','AAA' ,null union all
select '001','AAA' ,null union all
select '001','AAA' ,null union all
select '002','BBB' ,null union all
select '003','CCC' ,null union all
select '002','BBB' ,null
go
--查询
update t
set B=rk
from (select B,rk=rank() over(order by name ) from tb)tselect * from tb order by b
--结果
/*(6 行受影响)(6 行受影响)
id code name B
----------- ---------- -------------------- -----------
1 001 AAA 1
2 001 AAA 1
3 001 AAA 1
4 002 BBB 4
6 002 BBB 4
5 003 CCC 6(6 行受影响)
*/