你说的这个东西和排序有关,不同的排序,所在的位置不同,参考如下: 表jh03有下列数据: name score aa 99 bb 56 cc 56 dd 77 ee 78 ff 76 gg 78 ff 501. 名次生成方式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 62. 名次生成方式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
没有排序的,有id列,id列的值是Nvarchar 的 是:GUID值
那你用 1楼说的 row_number() over(order by id) 做吧id - 1 id + 1 表示上一行和后一行
1.从数据库获取了某条记录,如何获取该记录在数据库中的位置就是他在数据库中是第多少条记录?if object_id('dbo.#') is not null drop table dbo.# create table #(id int identity(1,1), name varchar(50), score varchar(50)) insert into # select 'aa', '99' union all select 'bb', '56' union all select 'cc', '56' union all select 'dd', '77' union all select 'ee', '78' union all select 'ff', '76' union all select 'gg', '78' union all select 'ff', '50'; with louis as( select t.id, t.name, t.score from # t ) select l.id, l.name, l.score, Row_Number() Over(order by id asc) as 字段遍号 from louis l;
2.从数据库获取了某条记录,如何获取该记录周围的各一条记录,即他的上一条记录,和下一条记录。 这。 还没找到解决方案,这是获取上下记录的编号的。 if object_id('dbo.#') is not null drop table dbo.# create table #(id int identity(1,1), name varchar(50), score varchar(50)) insert into # select 'aa', '99' union all select 'bb', '56' union all select 'cc', '56' union all select 'dd', '77' union all select 'ee', '78' union all select 'ff', '76' union all select 'gg', '78' union all select 'ff', '50'; with louis as( select t.id, t.name, t.score from # t ) select l.id, l.name, l.score, Row_Number() Over(order by id asc) as 记录遍号, ((Row_Number() Over(order by id asc)) - 1) as 上一条记录编号, ((Row_Number() Over(order by id asc)) + 1) as 下一条记录编号 from louis l;
access数据库 有相关的方法吗??
加入identity自增列。然后再进行操作!1>select row_number() over(order by id) id from test_table where 某字段='值';
你说的这个东西和排序有关,不同的排序,所在的位置不同,参考如下:
表jh03有下列数据:
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式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 62. 名次生成方式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
做吧id - 1
id + 1 表示上一行和后一行
create table #(id int identity(1,1), name varchar(50), score varchar(50))
insert into # select 'aa', '99' union all
select 'bb', '56' union all
select 'cc', '56' union all
select 'dd', '77' union all
select 'ee', '78' union all
select 'ff', '76' union all
select 'gg', '78' union all
select 'ff', '50';
with louis as(
select t.id, t.name, t.score from # t
)
select l.id, l.name, l.score, Row_Number() Over(order by id asc) as 字段遍号 from louis l;
这。 还没找到解决方案,这是获取上下记录的编号的。
if object_id('dbo.#') is not null drop table dbo.#
create table #(id int identity(1,1), name varchar(50), score varchar(50))
insert into # select 'aa', '99' union all
select 'bb', '56' union all
select 'cc', '56' union all
select 'dd', '77' union all
select 'ee', '78' union all
select 'ff', '76' union all
select 'gg', '78' union all
select 'ff', '50';
with louis as(
select t.id, t.name, t.score from # t
)
select l.id, l.name, l.score, Row_Number() Over(order by id asc) as 记录遍号, ((Row_Number() Over(order by id asc)) - 1) as 上一条记录编号, ((Row_Number() Over(order by id asc)) + 1) as 下一条记录编号 from louis l;