日期:2014-05-18  浏览次数:20446 次

怎样过滤连续重复的数据呢?
ID 经度 纬度
1 116.1 36.1
2 116.2 36.1
3 116.2 36.1
4 116.2 36.1
5 116.3 36.1
6 116.2 36.1


我想要他出来
1 116.1 36.1
2 116.2 36.1
5 116.3 36.1
6 116.2 36.1

------解决方案--------------------
SQL code

select min(id) id,经度,纬度
from tb
group by 经度,纬度

------解决方案--------------------
0.0 我看错了,1楼不行。

SQL code

;with ach as
(
    select *,rid=row_number() over (order by getdate()),
             pid=row_number() over (partition by 经度,纬度 order by id)
    from tb
)

select *
from ach t
where not exists (select 1 from ach where rid-pid=t.rid-t.pid and id < t.id)

------解决方案--------------------
SQL code

--> 测试数据:[TBL]
go
if object_id('[TBL]') is not null 
drop table [TBL]
go
create table [TBL](
[ID] int,
[经度] numeric(4,1),
[纬度] numeric(3,1)
)
go
insert [TBL]
select 1,116.1,36.1 union all
select 2,116.2,36.1 union all
select 3,116.2,36.1 union all
select 4,116.2,36.1 union all
select 5,116.3,36.1 union all
select 6,116.2,36.1

;WITH T
AS
(
SELECT *,ROW_NUMBER()OVER(ORDER BY GETDATE()) AS NUM,
ROW_NUMBER()OVER(PARTITION BY [经度],[纬度] ORDER BY [ID] ) AS [ORDER]
FROM [TBL]
)
SELECT [ID],[经度],[纬度] FROM T WHERE [ORDER]=1
UNION
SELECT [ID],[经度],[纬度] FROM T WHERE ID-[ORDER]<>1

/*
ID    经度    纬度
1    116.1    36.1
2    116.2    36.1
5    116.3    36.1
6    116.2    36.1
*/