求sql语句.在线等

[wangkenping]

A1表结构
lsh fph1 fph2
1 1 500
2 501 900
3 1500 2000
4 901 1200

怎么样生成如下数据
lsh fph1 fph2
1 1 1200
3 1500 2000

[WangZWang]

看不出你是有规律得来的,说说得来的方法

[xhwly]

看不出什麼規律啊!

[wangkenping]

哦 是取连续啊 比如 1-500,501-900
合成1-900

[点点星灯]

请问楼主,得到下面的数据,你的fph2有什么要求,看不出一点关系啊
请说明数据是怎么得到的???

lsh fph1 fph2
1 1 1200
3 1500 2000

[fengfangfang]

看出来了
fph2 + 1如果在fph1中有，就往下找

就是不会写

[fengfangfang]

用游标和递归存储过程

[zjcxc]

--示例

--示例数据
create table A1(lsh int,fph1 int,fph2 int)
insert A1 select 1,1 , 500
union all select 2,501 ,900
union all select 3,1500,2000
union all select 4,901 ,1200
go

--查询
select lsh,fph1,fph2=(
select min(fph2) from A1 aa
where fph1>=a.fph1 and not exists(
select * from A1 where fph1=aa.fph2+1))
from A1 a
where not exists(
select * from A1 where fph2=a.fph1-1)
go

--删除测试
drop table A1

/*--结果
lsh fph1 fph2
----------- ----------- -----------
1 1 1200
3 1500 2000

（所影响的行数为 2 行）
--*/

[realgz01]

楼上的 试试

create table A1(lsh int,fph1 int,fph2 int)
insert A1 select 1,1 , 500
union all select 2,501 ,900
union all select 3,1500,2000
union all select 4,901 ,1200
union all select 5,401 ,500
union all select 6,501,3000
union all select 7,44 ,55

[子陌红尘]

create table #T(lsh INT,fph1 INT,fph2 INT)
INSERT INTO #T SELECT 1,1 ,500
INSERT INTO #T SELECT 2,501 ,900
INSERT INTO #T SELECT 3,1500,2000
INSERT INTO #T SELECT 4,901 ,1200

SELECT
C.fph1,fph2 = MIN(D.fph2)
FROM
(SELECT A.fph1 FROM #T A WHERE NOT EXISTS(SELECT 1 FROM #T WHERE fph2 = A.fph1-1)) C,
(SELECT A.fph2 FROM #T A WHERE NOT EXISTS(SELECT 1 FROM #T WHERE fph1 = A.fph2+1)) D
WHERE
C.fph1<=D.fph2
GROUP BY
C.fph1

[realgz01]

写错了,汗，一点进步也有。
create table A1(lsh int,fph1 int,fph2 int)
insert A1 select 1,1 , 500
union all select 2,501 ,900
union all select 3,1500,2000
union all select 4,901 ,1200
union all select 5,401 ,600
union all select 6,601,3000
union all select 7,44 ,55

[子陌红尘]

--生成测试数据
create table #T(lsh INT,fph1 INT,fph2 INT)
INSERT INTO #T SELECT 1,1 ,500
INSERT INTO #T SELECT 2,501 ,900
INSERT INTO #T SELECT 3,1500,2000
INSERT INTO #T SELECT 4,901 ,1200

--执行查询
SELECT
C.lsh,C.fph1,fph2 = MIN(D.fph2)
FROM
(SELECT A.lsh,A.fph1 FROM #T A WHERE NOT EXISTS(SELECT 1 FROM #T WHERE fph2 = A.fph1-1)) C,
(SELECT A.fph2 FROM #T A WHERE NOT EXISTS(SELECT 1 FROM #T WHERE fph1 = A.fph2+1)) D
WHERE
C.fph1<=D.fph2
GROUP BY
C.lsh,C.fph1

--输出结果
lsh fph1 fph2
-------------------
1 1 1200
3 1500 2000

[点点星灯]

zjcxc(邹建) 　的方法好简单啊，学习！！

[realgz01]

libin_ftsafe(子陌红尘):有点意思,就是还不是完全的循环思想,还是是截前截后。

都是假定任意数据段没有交叉数据啊，如果可以这样的话，解法倒还真多~~~~

不然一句话的我只觉得似乎有一种办法，先把数据打散了再凑回来，效率么，就惨得不得了了。

[zjcxc]

to: realgz01(马甲)
按你写的数据,你认为该什么结果呢? 你的数据形成了数据交叉,怎么判断连续性?

还是楼主出来解决吧

[子陌红尘]

to：realgz01(马甲)

即使数据存在交叉，改改查询语句也是可以解决的：
---------------------------------------------------------------------------------
--生成测试数据
create table #T(lsh INT,fph1 INT,fph2 INT)
INSERT INTO #T SELECT 1 ,1 ,500
INSERT INTO #T SELECT 2 ,501 ,900
INSERT INTO #T SELECT 3 ,1500,2000
INSERT INTO #T SELECT 4 ,901 ,1200
INSERT INTO #T SELECT 5 ,2020 ,2200
INSERT INTO #T SELECT 6 ,2180 ,2300
INSERT INTO #T SELECT 7 ,2280 ,2400
INSERT INTO #T SELECT 8 ,2500 ,2600
INSERT INTO #T SELECT 9 ,2601 ,2700
INSERT INTO #T SELECT 10,2688 ,2800
INSERT INTO #T SELECT 10,2681 ,2780

--执行查询
SELECT
C.lsh,C.fph1,fph2 = MIN(D.fph2)
FROM
(SELECT A.lsh,A.fph1 FROM #T A WHERE NOT EXISTS(SELECT 1 FROM #T WHERE (A.fph1-1) between fph1 and fph2)) C,
(SELECT A.fph2 FROM #T A WHERE NOT EXISTS(SELECT 1 FROM #T WHERE (A.fph2+1) between fph1 and fph2)) D
WHERE
C.fph1<=D.fph2
GROUP BY
C.lsh,C.fph1