27,579
社区成员
发帖
与我相关
我的任务
分享
with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from 表名)
SELECT * FROM temptbl
select identity(int,1,1) as id,* into # from 表
select * from #
drop #
漏了一点不好意思
select id1=(select count(1)+1 from (select title,page_code,type,text,url from pages a,links b where a.code=b.code)c where id<d.id),* from
(select title,page_code,type,text,url from pages a,links b where a.code=b.code)d
order by page_code,
id
select id1=(select count(1) from (select title,page_code,type,text,url from pages a,links b where a.code=b.code)c where id<d.id),* from
(select title,page_code,type,text,url from pages a,links b where a.code=b.code)d
order by page_code,
id
表jh03有下列数据:
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 50
1. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 6
2. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8