表结构如下
Serial_No value
1 0.5
2 0.7
3 2.3
4 2.28
5 2.2
6 0.5
7 0.4应该得出的结构如下
serial_no
2
5
2.3与0.7的差值大于1,取任何一个serial_no就可以了, 我这儿取的是2
2.2与0.5的差值大于1,取任何一个serial_no ,我这儿取的是5,请问这个查询语句怎么写
Serial_No value
1 0.5
2 0.7
3 2.3
4 2.28
5 2.2
6 0.5
7 0.4应该得出的结构如下
serial_no
2
5
2.3与0.7的差值大于1,取任何一个serial_no就可以了, 我这儿取的是2
2.2与0.5的差值大于1,取任何一个serial_no ,我这儿取的是5,请问这个查询语句怎么写
insert into tb values(1 ,0.5)
insert into tb values(2 ,0.7)
insert into tb values(3 ,2.3)
insert into tb values(4 ,2.28)
insert into tb values(5 ,2.2)
insert into tb values(6 ,0.5)
insert into tb values(7 ,0.4)
goselect m.* from tb m, tb n where m.Serial_No = n.Serial_No - 1 and abs(m.value-n.value) > 1drop table tb/*
Serial_No value
----------- --------------------
2 .70
5 2.20(所影响的行数为 2 行)
*/
on a.Serial_No+1=b.Serial_No where b.value-a.value>1
if object_id('tb') is not null drop table tb
go
create table tb
(
Serial_No int identity(1,1) primary key,
value decimal(19,2)
)
insert into tb select 0.5
insert into tb select 0.7
insert into tb select 2.3
insert into tb select 2.28
insert into tb select 2.2
insert into tb select 0.5
insert into tb select 0.4
goselect Serial_No from tb b
where exists(select * from tb where Serial_No=b.Serial_No+1 and abs(b.value-value)>1)Serial_No
-----------
2
5(2 行受影响)
insert into tb values(1 ,0.5)
insert into tb values(2 ,0.7)
insert into tb values(3 ,2.3)
insert into tb values(4 ,2.28)
insert into tb values(5 ,2.2)
insert into tb values(6 ,0.5)
insert into tb values(7 ,0.4)
go
--如果Serial_No自然连续
select m.* from tb m, tb n where m.Serial_No = n.Serial_No - 1 and abs(m.value-n.value) > 1--如果Serial_No不自然连续
select m.* from
(select t.* , px = (select count(1) from tb where Serial_No < t.Serial_No) + 1 from tb t) m,
(select t.* , px = (select count(1) from tb where Serial_No < t.Serial_No) + 1 from tb t) n
where m.px = n.px - 1 and abs(m.value-n.value) > 1--如果是sql 2005,则用row_number
select m.* from
(select t.* , px = row_number() over(order by Serial_No) from tb t) m,
(select t.* , px = row_number() over(order by Serial_No) from tb t) n
where m.px = n.px - 1 and abs(m.value-n.value) > 1drop table tb/*
Serial_No value
----------- --------------------
2 .70
5 2.20(所影响的行数为 2 行)
*/
cross apply (select mx=max(value),mn=min(value) from tbl)A
where abs(value - mx)>1 or abs(value - mn)>1
FROM dbo.TT LEFT OUTER JOIN
(SELECT Serial_No + 1 AS Id2, value
FROM dbo.TT AS TT_2) AS tab2 ON tab2.Id2 = dbo.TT.Serial_No LEFT OUTER JOIN
(SELECT Serial_No - 1 AS Id1, value
FROM dbo.TT AS TT_1) AS tab1 ON tab1.Id1 = dbo.TT.Serial_No
TT为你的表明 这样V1就是上一行的值 V2为下一行的值 你可以根据自己需求判断来取