表A
id, Start Finish
1 1 2
2 2 3
3 2.5 4
4 6 7
5 7 9
6 10 12能不能出来这种结果 Start Finish
1 4
6 9
10 12也就是说类似找中间有空的值,像ID=3的 Start=2.5在ID=2之间了,也算在里面
id, Start Finish
1 1 2
2 2 3
3 2.5 4
4 6 7
5 7 9
6 10 12能不能出来这种结果 Start Finish
1 4
6 9
10 12也就是说类似找中间有空的值,像ID=3的 Start=2.5在ID=2之间了,也算在里面
INSERT @TB
SELECT '1', 1, 2 UNION ALL
SELECT '2', 2, 3 UNION ALL
SELECT '3', 2.5, 4 UNION ALL
SELECT '4', 6, 7 UNION ALL
SELECT '5', 7, 9 UNION ALL
SELECT '6', 10, 12SELECT Start,Finish
FROM @TB AS A
WHERE NOT EXISTS(SELECT 1 FROM @TB AS B WHERE A.id<>B.id AND A.Start BETWEEN B.Start AND B.Finish)
/*
Start Finish
--------------------------------------- -----------
1.0 2
6.0 7
10.0 12
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[Start] numeric(3,1),[Finish] int)
insert [A]
select 1,1,2 union all
select 2,2,3 union all
select 3,2.5,4 union all
select 4,6,7 union all
select 5,7,9 union all
select 6,10,12
---查询---
SELECT
ID
FROM
A T
WHERE
NOT EXISTS(SELECT 1 FROM A WHERE A.ID<T.ID AND T.START BETWEEN A.START AND A.FINISH)---结果---
ID
-----------
1
4
6(所影响的行数为 3 行)
这样?
SELECT
START
FROM
A T
WHERE
NOT EXISTS(SELECT 1 FROM A WHERE A.ID<>T.ID AND T.START BETWEEN A.START AND A.FINISH)---结果---
START
-----
1.0
6.0
10.0(所影响的行数为 3 行)
DECLARE @TB TABLE([id] VARCHAR(1), [Start] DECIMAL(10,1), [Finish] INT)
INSERT @TB
SELECT '1', 1, 2 UNION ALL
SELECT '2', 2, 3 UNION ALL
SELECT '3', 2.5, 4 UNION ALL
SELECT '4', 6, 7 UNION ALL
SELECT '5', 7, 9 UNION ALL
SELECT '6', 10, 12;WITH CTE AS
(
SELECT id,Start,Finish,SEQ=ROW_NUMBER() OVER (ORDER BY id)
FROM @TB AS A
WHERE NOT EXISTS(SELECT 1 FROM @TB AS B WHERE A.id<>B.id AND A.Start BETWEEN B.Start AND B.Finish)
)SELECT C1.Start,
Finish=ISNULL((SELECT Finish FROM @TB WHERE id=C2.id-1),(SELECT Finish FROM @TB WHERE id=C1.id))
FROM CTE AS C1 LEFT JOIN CTE AS C2
ON C1.SEQ=C2.SEQ-1
/*
Start Finish
--------------------------------------- -----------
1.0 4
6.0 9
10.0 12
*/
DECLARE @TB TABLE([id] VARCHAR(1), [Start] DECIMAL(10,1), [Finish] INT)
INSERT @TB
SELECT '1', 1, 2 UNION ALL
SELECT '2', 2, 3 UNION ALL
SELECT '3', 2.5, 4 UNION ALL
SELECT '4', 6, 7 UNION ALL
SELECT '5', 7, 9 UNION ALL
SELECT '6', 10, 12SELECT id,Start,Finish,SEQ=IDENTITY(int,1,1)
INTO #
FROM @TB AS A
WHERE NOT EXISTS(SELECT 1 FROM @TB AS B WHERE A.id<>B.id AND A.Start BETWEEN B.Start AND B.Finish)
SELECT C1.Start,
Finish=ISNULL((SELECT Finish FROM @TB WHERE id=C2.id-1),(SELECT Finish FROM @TB WHERE id=C1.id))
FROM # AS C1 LEFT JOIN # AS C2
ON C1.SEQ=C2.SEQ-1DROP TABLE #
/*
Start Finish
--------------------------------------- -----------
1.0 4
6.0 9
10.0 12
*/
(
[id] int ,
[Start] int ,
[Finish] int
)
set nocount on
insert @tData values( 1, 1, 2 )
insert @tData values( 2, 2, 3 )
insert @tData values( 3, 2.5, 4 )
insert @tData values( 4, 6, 7 )
insert @tData values( 5, 7, 9 )
insert @tData values( 6, 10, 12 )declare @tMid table
(
[id] int ,
[Start] int ,
[Finish] int ,
[fnID] int identity not null
)insert into @tMid
select ID, Start,Finish
from @tData as z
where not exists
(select 1
from @tData as m
where z.id > m.id and
z.Start between m.Start and m.Finish
)select z.Start,
isnull( (select Finish from @tData where id=m.id-1), (select Finish from @tData where id=z.id) ) as Finish
from @tMid as z
left join @tMid as m
on z.fnID = m.fnID-1set nocount off-- 执行结果
Start Finish
----------- -----------
1 4
6 9
10 12
declare @A table(id int, Start decimal(18,1), Finish decimal(18,1))
insert into @A
SELECT 1, 1, 2 UNION ALL
SELECT 2, 2, 3 UNION ALL
SELECT 3, 2.5, 4 UNION ALL
SELECT 4, 6, 7 UNION ALL
SELECT 5, 7, 9 UNION ALL
SELECT 6, 10, 12 ;with cte as
(
select *,px = row_number() over(order by Start,Finish) from @A
)
,cte1 as
(
select *,type = 1 from cte where px = 1
union all
select a.*,type = case when a.start<= b.finish then b.type else b.type+1 end from cte a,cte1 b where a.px = b.px+1
)
select start,finish from
(
select min(start) as start,max(finish) as finish,type from cte1 group by type
)T
order by start/*
1.0 4.0
6.0 9.0
10.0 12.0
/
如
1 1 2
2 2 3.5
3 3 5
4 6 7
我会从1开始找,找到下一条记录 发现第二条的记录start与我第一条的记录的finish = 2 能连接上.我就接着找
找到第3条,发现3<3.5.也能连接上。所以继续找,找到下一条6,发现6>5。所以不找了想上面的记录应该会处理成2条记录。
1 5
6 7
1 1 2 1
2 2 3.5 1
3 3 5 2
4 6 7 2如果我再加一个分组怎么求呢
例如,上面的:
结果应该为
start finish groupid
1 3.5 1
3 5 2
6 7 2
INSERT @TB
SELECT 1, 1, 2, 1 UNION ALL
SELECT 2, 2, 3.5, 1 UNION ALL
SELECT 3, 3, 5, 2 UNION ALL
SELECT 4, 6, 7, 2SELECT id,Start,Finish,[groupid],SEQ=IDENTITY(int,1,1)
INTO #
FROM @TB AS A
WHERE NOT EXISTS(SELECT 1 FROM @TB AS B WHERE B.[groupid]=A.[groupid] AND A.id<>B.id AND A.Start BETWEEN B.Start AND B.Finish)
SELECT C1.Start,
Finish=ISNULL((SELECT Finish FROM @TB WHERE id=C2.id-1),(SELECT Finish FROM @TB WHERE id=C1.id)),
C1.[groupid]
FROM # AS C1 LEFT JOIN # AS C2
ON C1.SEQ=C2.SEQ-1DROP TABLE #
/*
Start Finish groupid
----------- --------------------------------------- -----------
1 3.5 1
3 5.0 2
6 7.0 2*/