22,207
社区成员
发帖
与我相关
我的任务
分享
WITH CTE
AS
(SELECT *,COUNT(*) OVER (PARTITION BY LONGITUDE,LATITUDE) AS QTY,
ROW_NUMBER() OVER (PARTITION BY LONGITUDE,LATITUDE ORDER BY ID) AS SEQ_1,
ROW_NUMBER() OVER (PARTITION BY LONGITUDE,LATITUDE ORDER BY ID DESC) AS SEQ_2
FROM T)
SELECT * FROM CTE
WHERE QTY>=3 AND (SEQ_1=1 OR SEQ_2=1)
create table t (id int , Longitude int , Latitude int )
go
insert t select 435 ,1 ,2
union select 436 ,1 ,2
union select 437 ,1 ,3
union select 438 ,1 ,3
union select 439 ,1 ,3
union select 569 ,1 ,3
union select 577 ,1 ,3
union select 581 ,1 ,4
union select 583 ,1 ,4
union select 584 ,1 ,4
union select 587 ,1 ,5
union select 589 ,1 ,6
union select 591 ,1 ,6
union select 594 ,1 ,6
union select 596 ,1 ,6
union select 598 ,1 ,6
union select 7135 ,1 ,6
union select 7136,1 ,3
union select 7137 ,1 ,3
union select 7138,1 ,3
union select 7139,1 ,3
union select 7140 ,1 ,4
union select 7141,1 ,4
union select 7142,1 ,4
go
-- 试试这样
with cet
as(
select id2 - id1 flag ,min(id) id_1 , max(id) id_2
from (select id1 = row_number() over(partition by Latitude order by id) ,id2 = row_number() over( order by id) ,* from t ) a
group by id2 - id1
)
select distinct t.* from t join cet b on t.id = b.id_1 or t.id = b.id_2
go
drop table t
insert t select 435 ,1 ,2
union select 436 ,1 ,2
union select 437 ,1 ,3
union select 438 ,1 ,3
union select 439 ,1 ,3
union select 569 ,1 ,3
union select 577 ,1 ,3
union select 581 ,1 ,4
union select 583 ,1 ,4
union select 584 ,1 ,4
union select 587 ,1 ,5
union select 589 ,1 ,6
union select 591 ,1 ,6
union select 594 ,1 ,6
union select 596 ,1 ,6
union select 598 ,1 ,6
union select 7135 ,1 ,6
union select 7136,1 ,3
union select 7137 ,1 ,3
union select 7138,1 ,3
union select 7139,1 ,3
union select 7140 ,1 ,4
union select 7141,1 ,4
union select 7142,1 ,4
insert t select 435 ,1 ,2
union select 436 ,1 ,2
union select 437 ,1 ,3
union select 438 ,1 ,3
union select 439 ,1 ,3
union select 569 ,1 ,3
union select 577 ,1 ,3
union select 581 ,1 ,4
union select 583 ,1 ,4
union select 584 ,1 ,4
union select 587 ,1 ,5
union select 589 ,1 ,6
union select 591 ,1 ,6
union select 594 ,1 ,6
union select 596 ,1 ,6
union select 598 ,1 ,6
union select 7135 ,1 ,6
union select 7136,1 ,3
union select 7137 ,1 ,3
union select 7138,1 ,3
union select 7139,1 ,3
union select 7140 ,1 ,4
union select 7141,1 ,4
union select 7142,1 ,4
如果是这种数据的话,出来的结果就不对呀!
create table t (id int , Longitude int , Latitude int )
go
insert t
select 435 ,1 ,2
union select 436 ,1 ,2
union select 437 ,1 ,3
union select 438 ,1 ,3
union select 439 ,1 ,3
union select 569 ,1 ,3
union select 577 ,1 ,3
union select 581 ,1 ,4
union select 583 ,1 ,4
union select 584 ,1 ,4
union select 587 ,1 ,5
union select 589 ,1 ,6
union select 591 ,1 ,6
union select 594 ,1 ,6
union select 596 ,1 ,6
union select 598 ,1 ,6
union select 7135 ,1 ,6
go
select distinct * from t a join (select Longitude,Latitude,min(id) id1 , max(id) id2 from t group by Longitude,Latitude ) b on a.id = b.id1 or a.id = b.id2
go
drop table t
/*
id Longitude Latitude Longitude Latitude id1 id2
435 1 2 1 2 435 436
436 1 2 1 2 435 436
437 1 3 1 3 437 577
577 1 3 1 3 437 577
581 1 4 1 4 581 584
584 1 4 1 4 581 584
587 1 5 1 5 587 587
589 1 6 1 6 589 7135
7135 1 6 1 6 589 7135
*/