--示例--示例数据 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 行) --*/
楼上的 试试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 ,1200SELECT 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
写错了,汗,一点进步也有。 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
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
fph2 + 1如果在fph1中有,就往下找就是不会写
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 行)
--*/
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
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 ,1200SELECT
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
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
按你写的数据,你认为该什么结果呢? 你的数据形成了数据交叉,怎么判断连续性? 还是楼主出来解决吧
---------------------------------------------------------------------------------
--生成测试数据
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