取不连续的号码

[qqzeng-ip]

字段 phone:

13706143678
13805399290

15633137726
15633137751
15633137795
15633137870
15633137891
15633137897
15633137899
15633137925

14723450975
14923450975

15181362125
15181362128
15181362129
15181362131
15181362135
15181362139

13923450975

号码前六位相同（SUBSTRING (phone,0, 7)）视为连续，要提取 不连续的号码 结果：

13706143678
13805399290
14723450975
14923450975
13923450975

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([phone] bigint)
insert [tb]
select 13706143678 union all
select 13805399290 union all
select 15633137726 union all
select 15633137751 union all
select 15633137795 union all
select 15633137870 union all
select 15633137891 union all
select 15633137897 union all
select 15633137899 union all
select 15633137925 union all
select 14723450975 union all
select 14923450975 union all
select 15181362125 union all
select 15181362128 union all
select 15181362129 union all
select 15181362131 union all
select 15181362135 union all
select 15181362139 union all
select 13923450975
go

select * from tb t
where (select count(1) from tb where left(phone,7)=left(t.phone,7))=1

/**
phone
--------------------
13706143678
13805399290
14723450975
14923450975
13923450975

(5 行受影响)
**/

[百年树人]

--or
select * from tb t
where not exists(select 1 from tb where left(phone,7)=left(t.phone,7) and phone!=t.phone)

[qqzeng-ip]

如果 要剔除 条件：连续的号码的个数>n （如 3）的呢

[百年树人]

[Quote=引用 3 楼 的回复:]
如果 要剔除 条件：连续的号码的个数>n （如 3）的呢
[/Quote]

select * from tb t
where (select count(1) from tb where left(phone,7)=left(t.phone,7))=1

把这里的=1改成你要的条件即可

[qqzeng-ip]

我这里有4十万数据 用这种执行 花了很久时间 4分钟左右 才执行完 有没有 更高效的方法呢

[百年树人]

[Quote=引用 5 楼 的回复:]
我这里有4十万数据 用这种执行 花了很久时间 4分钟左右 才执行完 有没有 更高效的方法呢
[/Quote]
try

select a.* from tb a
join (select left(phone,7) as p from tb group by left(phone,7) having count(1)>3) b
on left(a.phone,7)=b.p

[qqzeng-ip]

很好 很快 毫秒级 的了 谢谢！