declare @table table (city varchar(1),people int) insert into @table select 'd',1000 union all select 'b',1500 union all select 'c',2000 union all select 'a',3000declare @city varchar(2) set @city='c'declare @people int set @people=2000select city,people from (select row_number() over (order by city) as id,* from @table ) a where id=(select id-1 from (select row_number() over (order by city) as id,* from @table ) b where b.city=@city and b.people=@people) /* city people ---- ----------- b 1500 */
declare @table table (city varchar(1),people int) insert into @table select 'd',1000 union all select 'b',1500 union all select 'c',2000 union all select 'a',3000declare @city varchar(2) set @city='c'declare @people int set @people=2000;with temp as (select row_number() over (order by people) as id,* from @table ) select a.* from temp a,temp b where a.id = b.id - 1 and b.people = @people
with cte as ( select row_number() over(order by getdate()) as no,* from tb ) select city,people from cte where no=(select no from cte where city='c' and people=2000)-1
select top 1 * from tb where people < 2000 order by people desc
如果没有其他列来排序,不能保证一定查到,但可以用这个试试. create table tb(city nvarchar(10),people int) insert into tb select 'd',1000 insert into tb select 'b',1500 insert into tb select 'c',2000 insert into tb select 'a',3000 go select identity(int,1,1)as id,* into # from tb select a.city from # a inner join # b on a.id=b.id-1 where b.city='c' go drop table tb,# /* city ---------- b(1 行受影响) */
declare @table table (city varchar(1),people int)
insert into @table
select 'd',1000 union all
select 'b',1500 union all
select 'c',2000 union all
select 'a',3000declare @city varchar(2)
set @city='c'declare @people int
set @people=2000select city,people from
(select row_number() over (order by city) as id,* from @table
) a where id=(select id-1 from
(select row_number() over (order by city) as id,* from @table ) b
where b.city=@city and b.people=@people)
/*
city people
---- -----------
b 1500
*/
insert into @table
select 'd',1000 union all
select 'b',1500 union all
select 'c',2000 union all
select 'a',3000declare @city varchar(2)
set @city='c'declare @people int
set @people=2000;with temp as
(select row_number() over (order by people) as id,* from @table
)
select a.* from temp a,temp b where a.id = b.id - 1 and b.people = @people
with cte as
(
select row_number() over(order by getdate()) as no,* from tb
)
select city,people from cte
where no=(select no from cte where city='c' and people=2000)-1
create table tb(city nvarchar(10),people int)
insert into tb select 'd',1000
insert into tb select 'b',1500
insert into tb select 'c',2000
insert into tb select 'a',3000
go
select identity(int,1,1)as id,* into # from tb
select a.city from # a inner join # b on a.id=b.id-1 where b.city='c'
go
drop table tb,#
/*
city
----------
b(1 行受影响)
*/