求sql语句.在线等
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
问题点数：100、回复次数：15Top
1 楼WangZWang（先来）回复于 2005-05-27 11:06:23 得分 0
看不出你是有规律得来的,说说得来的方法Top
2 楼xhwly（wly）回复于 2005-05-27 11:07:20 得分 0
看不出什麼規律啊!
Top
3 楼wangkenping（找有感觉的妹妹）回复于 2005-05-27 11:09:16 得分 0
哦 是取连续啊 比如 1-500,501-900
合成1-900Top
4 楼aw511（点点星灯）回复于 2005-05-27 11:09:56 得分 0
请问楼主,得到下面的数据,你的fph2有什么要求,看不出一点关系啊
请说明数据是怎么得到的???
lsh fph1 fph2
1 1 1200
3 1500 2000Top
5 楼fengfangfang（）回复于 2005-05-27 11:10:08 得分 0
看出来了
fph2 + 1如果在fph1中有，就往下找
就是不会写
Top
6 楼fengfangfang（）回复于 2005-05-27 11:11:44 得分 0
用游标和递归存储过程Top
7 楼zjcxc（邹建）回复于 2005-05-27 11:28:21 得分 100
--示例
--示例数据
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 行）
--*/Top
8 楼realgz01（官方马甲）回复于 2005-05-27 11:33:53 得分 0
楼上的 试试
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 ,55Top
9 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-05-27 11:34:43 得分 0
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.fph1Top
10 楼realgz01（官方马甲）回复于 2005-05-27 11:37:06 得分 0
写错了,汗，一点进步也有。
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 ,55Top
11 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-05-27 11:38:33 得分 0
--生成测试数据
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
Top
12 楼aw511（点点星灯）回复于 2005-05-27 11:40:07 得分 0
zjcxc(邹建) 的方法好简单啊，学习！！Top
13 楼realgz01（官方马甲）回复于 2005-05-27 11:43:45 得分 0
libin_ftsafe(子陌红尘):有点意思,就是还不是完全的循环思想,还是是截前截后。
都是假定任意数据段没有交叉数据啊，如果可以这样的话，解法倒还真多~~~~
不然一句话的我只觉得似乎有一种办法，先把数据打散了再凑回来，效率么，就惨得不得了了。Top
14 楼zjcxc（邹建）回复于 2005-05-27 11:49:18 得分 0
to: realgz01(马甲)
按你写的数据,你认为该什么结果呢? 你的数据形成了数据交叉,怎么判断连续性?
还是楼主出来解决吧Top
15 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-05-27 12:35:25 得分 0
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.fph1Top
