难题,呵呵
想用一条sql语句实现相邻两条记录之间某个字段的差大于等于60000以上的纪录。
想请教一下又没有办法写出来?
[解决办法]
create table #
(
id int identity(1,1),
qty int
)
insert into #
select 2 union all
select 60002 union all
select 0 union all
select 0 union all
select 0 union all
select 200 union all
select 70000 union all
select 100
select a.* from # a
where abs(a.qty -(select top 1 qty from # where id> a.id order by id ))> =60000
and a.id <(select top 1 id from # where id> a.id order by id )
id qty
----------- -----------
1 2
2 60002
6 200
7 70000
(4 row(s) affected)
[解决办法]
--将数据插入一临时表,生成一连号的ID
select id = identity(int,1,1) , * into tmp from tb
select a.* from tmp a , tmp b where abs(a.col - b.col) > 60000 and a.id = b.id + 1