表A内容如下:
ID DataValue
1 1
2 2
3 3
4 4
5 5
6 8
7 9
8 10
9 11
10 20
11 21
12 22现在要求相邻两行的DataValue列进行比较,把差值大于2的区域找出来,比如上面内容可找出DataValue(1,2,3,4,5),DataValue(8,9,10,11),DataValue(20,21,22)三个区域。怎样写该SQL语句呢?
ID DataValue
1 1
2 2
3 3
4 4
5 5
6 8
7 9
8 10
9 11
10 20
11 21
12 22现在要求相邻两行的DataValue列进行比较,把差值大于2的区域找出来,比如上面内容可找出DataValue(1,2,3,4,5),DataValue(8,9,10,11),DataValue(20,21,22)三个区域。怎样写该SQL语句呢?
from ta
group by DataValue-id
-- Author: happyflystone
-- Version:V1.001
-- Date:2009-08-13 22:49:19
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,DataValue int)
Go
Insert into ta
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,8 union all
select 7,9 union all
select 8,10 union all
select 9,11 union all
select 10,20 union all
select 11,21 union all
select 12,22
Go
--Start
select DataValue-id,ltrim(min(DataValue) )+'--'+ltrim(max(DataValue))
from ta
group by DataValue-id
--Result:
/*
----------- --------------------------
0 1--5
2 8--11
10 20--22(所影响的行数为 3 行)*/
--End
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(ID int, DataValue int )
go
insert tb SELECT 1, 1 UNION ALL SELECT
2, 2 UNION ALL SELECT
3, 3 UNION ALL SELECT
4, 4 UNION ALL SELECT
5, 5 UNION ALL SELECT
6, 8 UNION ALL SELECT
7, 9 UNION ALL SELECT
8, 10 UNION ALL SELECT
9, 11 UNION ALL SELECT
10, 20 UNION ALL SELECT
11, 21 UNION ALL SELECT
12, 22
go
select identity(int,1,1) as kid ,DataValue into #
from tb k
where not exists(select * from tb where k.DataValue=DataValue-1)
or not exists(select * from tb where k.DataValue=DataValue+1)
select
起点=a.DataValue,
终点=b.DataValue
from (select * from # where kid%2=1) a join (select * from # where kid%2=0) b
on a.kid=b.kid-1起点 终点
----------- -----------
1 5
8 11
20 22
INSERT TBTEST
SELECT 1 , 1 UNION ALL
SELECT 2 , 2 UNION ALL
SELECT 3 , 3 UNION ALL
SELECT 4 , 4 UNION ALL
SELECT 5 , 5 UNION ALL
SELECT 6 , 8 UNION ALL
SELECT 7 , 9 UNION ALL
SELECT 8 , 10 UNION ALL
SELECT 9 , 11 UNION ALL
SELECT 10 , 20 UNION ALL
SELECT 11 , 21 UNION ALL
SELECT 12 , 22
SELECT * FROM TBTEST
WHERE ID<(SELECT MIN(ID) FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.ID=ID+1 AND ABS(T.DataValue-DataValue)>2))
SELECT * FROM TBTEST
WHERE ID>=(SELECT MIN(ID) FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.ID=ID+1 AND ABS(T.DataValue-DataValue)>2))
AND ID<(SELECT MAX(ID) FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.ID=ID+1 AND ABS(T.DataValue-DataValue)>2))SELECT * FROM TBTEST
WHERE ID>=(SELECT MAX(ID) FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.ID=ID+1 AND ABS(T.DataValue-DataValue)>2))
ID DataValue
----------- -----------
1 1
2 2
3 3
4 4
5 5(所影响的行数为 5 行)ID DataValue
----------- -----------
6 8
7 9
8 10
9 11(所影响的行数为 4 行)ID DataValue
----------- -----------
10 20
11 21
12 22(所影响的行数为 3 行)