写了一个事例你看一下吧。declare @num as int = 100 ; with T as ( select number,paiming = ROW_NUMBER() over(order by number) from master..spt_values where type = 'P' ) select * from T where ABS( @num - number) <= 5
借用系统表,按绝对值小于等于5,是个思路,楼上的语句貌似只能支持sql2005
我用的是mysql,请问怎么写好
这样?先得出row=名次;with Cte as (select *,dense_rank()over(order by 名次) as row from 表名)select a.* from Cte as a ,Cte as b where b.Name='张三' and abs(a.row-b.row) between 1 AND 5--已存在名次列 select a.* from 表 as a ,表 as b where b.Name='张三' and abs(a.名次-b.名次) between 1 AND 5
;
with T as
(
select number,paiming = ROW_NUMBER() over(order by number)
from master..spt_values
where type = 'P'
)
select * from T
where ABS( @num - number) <= 5
as
(select *,dense_rank()over(order by 名次) as row from 表名)select a.* from Cte as a ,Cte as b
where b.Name='张三' and abs(a.row-b.row) between 1 AND 5--已存在名次列
select a.* from 表 as a ,表 as b
where b.Name='张三' and abs(a.名次-b.名次) between 1 AND 5
使用union将两个集合合成一个