高手看看，一个查询问题实在搞不定！

Question

现在手机都是11位两个号的判断：
declare @No varchar(20)
declare @No1 varchar(20)
set @No='13611111111'
set @No1='13711111111'select sum(case when substring(@No,t,1)=substring(@No1,t,1) then 0 else 1 end)
from (
select 1 as t
union all
select 2 as t
union all
select 3 as t
union all
select 4 as t
union all
select 5 as t
union all
select 6 as t
union all
select 7 as t
union all
select 8 as t
union all
select 9 as t
union all
select 10 as t
union all
select 11 as t
) as a
having sum(case when substring(@No,t,1)=substring(@No1,t,1) then 0 else 1 end)<=1

Answer 1

create function f_strCompare(@str1 varchar(20),@str2 varchar(20))
returns int  --返回值:0、号码完全相同；1、情侣号；2、非情侣号
as
begin
    declare @i int,@len int
    set @len=1
    set @i=0    if len(@str1)!=len(@str2)
        return 2
    
    while @len<=len(@str1)
    begin
        if substr(@str1,@len)!=substr(@str2,@len)
        begin
            if @i=1
                return 2 
            else 
                set @i=1
        end    
        set @len=@len+1
    end    return @i  
end
go

Answer 2

借用函数头create function f_strCompare(@No varchar(20),@No1 varchar(20))
returns int  --返回值:0、号码完全相同；1、情侣号；2、非情侣号
as
begin
declare @r intselect @r=sum(case when substring(@No,t,1)=substring(@No1,t,1) then 0 else 1 end)
from (
select 1 as t
union all
select 2 as t
union all
select 3 as t
union all
select 4 as t
union all
select 5 as t
union all
select 6 as t
union all
select 7 as t
union all
select 8 as t
union all
select 9 as t
union all
select 10 as t
union all
select 11 as t
) as aif @r>2
   set @r=2return @r
end
go

Answer 3

--不用函数declare @t table
(
mobile_no varchar(11)
)
insert into @t
select '13312345678' union all
select '13312345677' union all
select '13012345678' union all
select '13012345688' union all
select '13512345678' union all
select '13512349678' union all
select '13312368778' union all
select '13900010000'select * 
from @t a
where exists
(
select mobile_no
from @t
where isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,mobile_no))))-1) as bigint),0)=0
and a.mobile_no<>mobile_no
)/*
mobile_no   
----------- 
13312345678
13312345677
13012345678
13012345688
13512345678
13512349678
*/

Answer 4

如果为情侣号，2号相减的绝对值应为x00000或者x的形式，去掉头以后转为数字，不是0就是Null直接拿这个来做判断就可以了

Answer 5

LouisXIV(夜游神)说的很对！
但有个问题，如果有多组情侣号，这个查询在显示结果的时候，没有按组显示，而是按表的主键排序显示了。我在我这边运行的结果是id   telnum
12 13065030131
13 13605317729
24 13065030130
28 13505317729
29 13005317729如何让情侣号排在一起？

Answer 6

一对对显示行不行？select a.telnum,b.telnum
from tablename a,tablename b
where dbo.f_strCompare(a.telnum,b.telnum)=1
可能很多重复的

Answer 7

--为了写起来方便点，借用了一个临时表declare @t table
(
mobile_no varchar(11)
)
insert into @t
select '13065030131' union all
select '13605317729' union all
select '13065030130' union all
select '13505317729' union all
select '13005317729' union all
select '13512349678' union all
select '13312368778' union all
select '13900010000'select mobile_no into #
from @t a
where exists
(
select mobile_no
from @t
where isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,mobile_no))))-1) as bigint),0)=0
and a.mobile_no<>mobile_no
)select mobile_no
from # a
order by(
select distinct len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,mobile_no))))
from #
where isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,mobile_no))))-1) as bigint),0)=0
and a.mobile_no<>mobile_no),mobile_no/*
mobile_no   
----------- 
13065030130
13065030131
13005317729
13505317729
13605317729--临时表内写入符合情侣号的号码，然后再以临时表为基础排序
*/

Answer 8

引用夜游神的，检测是否情侣号的条件。 我把分组也写出来。
select '13515549678' union all
select '13515549778' union all
select '13900010000'select mobile_no,identity(int) id into #t from @tselect mobile_no,groupid=
case when 0<
(select count(1) from #t b where  isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))))-1) as bigint),0)=0 and b.id<a.id and a.mobile_no<>b.mobile_no)
then 
(select min(id) from #t b where  isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))))-1) as bigint),0)=0 and b.id<a.id and a.mobile_no<>b.mobile_no)
 else id end 
from #t a
where exists
(
select mobile_no
from #t b
where isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))))-1) as bigint),0)=0 and a.mobile_no<>b.mobile_no)
drop table #tid mobile_no groupid
1 13312345678 1
2 13312345677 1
3 13012345678 1
4 13012345688 3
5 13512345678 1
6 13515549678 6
7 13515549778 6因为存在一对多的，还有第三者插足成情侣的，所以造成了这样的结果。
因为:
1111
1112
是情侣
1111
1122
不是情侣
而1122 是二者的情侣， 所以出现了这样的情况。字段说明,groupid相同的，肯定是一组的，
还有groupid=n 的记录与 id=n的记录是情侣。
如果要写成1对1的，那么就是
declare @t table
(
mobile_no varchar(11)
)
insert into @t
select '13312345678' union all
select '13312345677' union all
select '13012345678' union all
select '13012345688' union all
select '13512345678' union all
select '13515549678' union all
select '13515549778' union all
select '13900010000'select mobile_no,identity(int) id into #t from @tselect id,mobile_no,groupid=
case when 0<
(select count(1) from #t b where  isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))))-1) as bigint),0)=0 and b.id<a.id and a.mobile_no<>b.mobile_no)
then 
(select min(id) from #t b where  isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))))-1) as bigint),0)=0 and b.id<a.id and a.mobile_no<>b.mobile_no)
 else id end into #base
from #t a
where exists
(
select mobile_no
from #t b
where isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))))-1) as bigint),0)=0 and a.mobile_no<>b.mobile_no)select a.mobile_no,b.mobile_no from #base a
inner join #base b
on a.id=b.groupiddrop table #base
drop table #t13312345678 13512345678
13312345678 13312345678
13312345678 13312345677
13312345678 13012345678
13012345678 13012345688
13515549678 13515549678
13515549678 13515549778

Answer 9

最上面粘的时候少粘了一点。就是定义表变量和写入测试数据的。declare @t table
(
mobile_no varchar(11)
)
insert into @t
select '13312345678' union all
select '13312345677' union all
select '13012345678' union all
select '13012345688' union all
select '13512345678' union all
select '13515549678' union all
select '13515549778' union all
select '13900010000'

Answer 10

最后将语句简化一下。
declare @t table
(
mobile_no varchar(11)
)
insert into @t(mobile_no)
select '13312345678' union all
select '13312345677' union all
select '13012345678' union all
select '13012345688' union all
select '13512345678' union all
select '13515549678' union all
select '13515549778' union all
select '13900010000'select mobile_no,identity(int) id into #t from @t
select id,mobile_no,
groupid=
case when 0<(select count(1) from #t b where len(replace(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no)),'0',''))=1 and b.id<a.id)
then 
(select min(id) from #t b where  len(replace(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no)),'0',''))=1 and b.id<a.id)
else id end 
into #base
from #t a
where exists
(
select 1 from #t b
where len(replace(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no)),'0',''))=1 and a.mobile_no<>b.mobile_no
)select a.mobile_no,b.mobile_no 
from #base a
inner join #base b
on a.id=b.groupid and a.id<>b.iddrop table #base
drop table #t如果楼主的原表中有标识列，那么可以不用临时表，一条SELECT取得最后结果。

Answer 11

--测试数据
select '13312345678' as mobile_no into dbo.#insert into # select '13312345679'
insert into # select '13312345677'
insert into # select '13012345678'
insert into # select '13012345688'
insert into # select '13512345678'
insert into # select '13512349678'select * from #
order by mobile_no
13012345678
13012345688
13312345677
13312345678
13312345679
13512345678
13512349678--选出13312345679的情侣号码
select mobile_no
from #
where len(replace(replace(str(abs(mobile_no - 13312345679)),'0',''),' ',''))=113312345677
13312345678--选出13012345688的情侣号码
select mobile_no
from #
where len(replace(replace(str(abs(mobile_no - 13012345688)),'0',''),' ',''))=113012345678

Answer 12

select '13312345678' as mobile_no into dbo.#insert into # select '13312345679'
insert into # select '13312345677'
insert into # select '13012345678'
insert into # select '13012345688'
insert into # select '13512345678'
insert into # select '13512349678'
insert into # select '13312385679'
insert into # select '13012345679'
insert into # select '13512345679'--13312345679
select *
from #
where replace(abs(13312345679-mobile_no),'0','') between 1 and 9/*13312345677
13312385679
13012345679
13512345679
13312345678*/godrop table #

Answer 13

各位高手真是太~~~强了！在大家的帮助下，我终于搞定了！基本上是按fcuandy(要学的东西还很多)大侠发的那个最长的贴子实现的，没用临时表，只建了个存储过程，执行结果很正确，而且还能根据生成的groupid排序。衷心感谢各位高手的帮忙，我这就给大家散分！

Answer 14

isnull(cast(right(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))),len(rtrim(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no))))-1) as bigint),0)=0 and a.mobile_no<>b.mobile_no这个条件可以简化成:len(replace(abs(convert(bigint,a.mobile_no)-convert(bigint,b.mobile_no)),'0',''))=1这样的。
因为如果是情侣号，那么相减的绝对值就是 n或 n0000这样的型式，将0替换为'',只留下n,也即len 这个字串长度为1.
如果不是情侣号，就有两个以上的位不同，相减后，替换掉0,len最少也是2

Answer 15

又出现一个新问题！
根据以上的思路，比较这两个号码13001230475
13001230468这两个数之差为7，根据sql语句会被认做是情侣号，但是有两位数不同。
规律总结如下：
当两个号码中有相邻的两位数不同时，对于这两个号码中的这两个不同的数，肯定是一大一小了
（比如上面的75和68）。如果大数的高位[7]被小数的高位[6]大1,而小数的低位[8]又大于大数的低位[5]，在此种情况下两者之差为1-9之间的某个数，如果这两个数出现在号码的其它位置，相减后的结果就是最高位为1-9间的某数，然后跟一串0,同样会被认做情侣号，但这个结果是错误的。

Answer 16

至于这么麻烦吗？
看看我这个简单的不能再简单的：select 13312345678 as mobile_no into dbo.#insert into # select 13312345679
insert into # select 13312345677
insert into # select 13012345678
insert into # select 13012345688
insert into # select 13512345678
insert into # select 13512349678
insert into # select 13312385679
insert into # select 13302385679
insert into # select 13012345679select t1.mobile_no, t2.mobile_no
from # t1, # t2
where (     abs(t1.mobile_no - t2.mobile_no) < 10 
        or (cast(abs(t1.mobile_no - t2.mobile_no) as int) % 10) = 0
      )
      and t1.mobile_no < t2.mobile_no
order by t1.mobile_no/*
13012345678 13012345688
13012345678 13512345678
13012345678 13512349678
13012345678 13012345679
13012345678 13312345678
13012345679 13312345679
13012345679 13012345688
13012345679 13312385679
13012345679 13302385679
13012345688 13512345678
13012345688 13512349678
13012345688 13312345678
13302385679 13312345679
13302385679 13312385679
13312345677 13312345679
13312345677 13312345678
13312345678 13312345679
13312345678 13512345678
13312345678 13512349678
13312345679 13312385679
13512345678 13512349678
*/godrop table #

Answer 17

根据Bandry(菜鸟－舍我其谁)再改动一下
select 13312345678 as mobile_no into dbo.#insert into # select 13312345679
insert into # select 13312345677
insert into # select 13012345678
insert into # select 13012345688
insert into # select 13512345678
insert into # select 13512349678
insert into # select 13312385679
insert into # select 13302385679
insert into # select 13012345679select t1.mobile_no, t2.mobile_no
from # t1, # t2
where 
(
(abs(t1.mobile_no - t2.mobile_no) < 10 
and substring(cast(t1.mobile_no as varchar), 10, 1) = substring(cast(t2.mobile_no as varchar), 10, 1))
 or (
cast(abs(t1.mobile_no - t2.mobile_no) as int) % 10 = 0) 
and len(replace(abs(t1.mobile_no - t2.mobile_no), '0', '')) = 1
      )
      and t1.mobile_no < t2.mobile_no
order by t1.mobile_nogodrop table #