关于手机号码的筛选查询语句。。。。。

[bluedreams85]

现在一批手机号，要区分最后几位是：ABAB、AABB、ABC、ABCD、AAAB、AABBCC、AAA、AAABBB、AAAA、AAAB这类。
如：187****8989这类是属于ABAB型。。其它的类似。。。请问数据库查询应该如何写。。。

[jinjazz]

写一自定义函数，里面做一堆case when就行了

[bluedreams85]

[Quote=引用 1 楼 jinjazz 的回复:]

写一自定义函数，里面做一堆case when就行了
[/Quote]
帅哥帮忙写个语句不懂。。。

[华夏小卒]

一个一个匹配
ABAB:
select * from tb 
where len(hm)=11
 and substring(hm,8,1)= substring(hm,10,1)

[dawugui]

把可能存在的类型做成一个表tb2。

然后
select * from tb1 where right(手机号,3) in (select haoma from tb2) or right(手机号,4) in (select haoma from tb2)

[bluedreams85]

[Quote=引用 3 楼 js_szy 的回复:]

SQL code
一个一个匹配
ABAB:
select * from tb
where len(hm)=11
and substring(hm,8,1)= substring(hm,10,1)
[/Quote]
呵呵，帅哥一起帮我写出来好了，一会给你好多分。。。

[bluedreams85]

[Quote=引用 4 楼 dawugui 的回复:]

把可能存在的类型做成一个表tb2。

然后
select * from tb1 where right(手机号,3) in (select haoma from tb2) or right(手机号,4) in (select haoma from tb2)
[/Quote]
不懂。。。。能不能帮我写出来。。。

[bluedreams85]

谁全部帮我写出来我给他1K分。。。

[dawugui]

[Quote=引用 7 楼 bluedreams85 的回复:]
谁全部帮我写出来我给他1K分。。。
[/Quote]
ABAB、AABB、ABC、ABCD、AAAB、AABBCC、AAA、AAABBB、AAAA、AAAB

1010
1212
1313
1414
...
1919
2010
2121
2323
...
2929

把你存在的所有可能的号码做成一个表。

[oO寒枫Oo]

这是体力劳动的

[oO寒枫Oo]

select * from tb  --ABAB
where len(num)=11 and substring(num,8,1)= substring(num,10,1) and substring(num,9,1)= substring(num,11,1)
union 
select * from tb  --AABB
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1)
union
select * from tb  --ABC
where len(num)=11 and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)
union
select * from tb  --ABCD
where len(num)=11 and cast(substring(num,8,1) as int)= (cast(substring(num,9,1)as int) +1) and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)

[fpzgm]

体力

[bluedreams85]

[Quote=引用 10 楼 lxpbs8851 的回复:]

SQL code

select * from tb --ABAB
where len(num)=11 and substring(num,8,1)= substring(num,10,1) and substring(num,9,1)= substring(num,11,1)
union
select * from tb --AABB
where len(num)=11 and sub……
[/Quote]
呵呵，谢谢大侠，继续帮我写完可以吗？因为四年没接触过SQL现在忘光了。以前也只懂得简单的。

[至尊贱客]

去网上搜sql的条件查询吧

[华夏小卒]

-- ABAB:
select * from tb 
where len(hm)=11
 and substring(hm,8,1)= substring(hm,10,1)

-- AABB

select * from tb 
where len(hm)=11
 and substring(hm,8,1)= substring(hm,9,1) 
 and substring(hm,10,1)= substring(hm,11,1)
 and substring(hm,8,1)<> substring(hm,10,1)
 
-- ABC
select * from tb 
where len(hm)=11
 and substring(hm,9,1)<>substring(hm,10,1) 
 and substring(hm,10,1)<> substring(hm,11,1)


-- ABCD
select * from tb 
where len(hm)=11
 and substring(hm,8,1) not in(substring(hm,9,1),substring(hm,10,1),substring(hm,11,1)) 
 and substring(hm,9,1)not in(substring(hm,10,1),substring(hm,11,1))
 and substring(hm,10,1)<> substring(hm,11,1) 


-- AAAB
select * from tb 
where len(hm)=11
 and substring(hm,8,1)=(substring(hm,9,1)
 and substring(hm,9,1)= substring(hm,10,1)  
 and substring(hm,10,1)<> substring(hm,11,1) 

-- AABBCC
select * from tb 
where len(hm)=11
 and substring(hm,6,1)=(substring(hm,7,1)
 and substring(hm,8,1)=(substring(hm,9,1)
 and substring(hm,10,1)=substring(hm,11,1) 
 and substring(hm,6,1)NOT IN(substring(hm,8,1) ,substring(hm,10,1))  
 AND substring(hm,8,1)<>substring(hm,10,1)

-- AAA
select * from tb 
where len(hm)=11
 and substring(hm,9,1)=substring(hm,10,1) 
 and substring(hm,10,1)=substring(hm,11,1)

-- AAABBB

select * from tb 
where len(hm)=11
 and substring(hm,6,1)=(substring(hm,7,1)
 and substring(hm,7,1)=(substring(hm,8,1)
 and substring(hm,9,1)=(substring(hm,10,1)
 and substring(hm,10,1)=substring(hm,11,1)  
 AND substring(hm,6,1)<>substring(hm,9,1)

--AAAA
select * from tb 
where len(hm)=11
 and substring(hm,9,1)=substring(hm,8,1) 
 and substring(hm,9,1)=substring(hm,10,1) 
 and substring(hm,10,1)=substring(hm,11,1)

--AAAB

最后一个，你自己写。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

[oO寒枫Oo]

select * from tb  --ABAB
where len(num)=11 and substring(num,8,1)= substring(num,10,1) and substring(num,9,1)= substring(num,11,1)
union 
select * from tb  --AABB
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1)
union
select * from tb  --ABC
where len(num)=11 and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)
union
select * from tb  --ABCD
where len(num)=11 and cast(substring(num,8,1) as int)= (cast(substring(num,9,1)as int) +1) and cast(substring(num,9,1) as int)= (cast(substring(num,10,1)as int) +1) and cast(substring(num,10,1) as int)= (cast(substring(num,11,1)as int) +1)
union 
select * from tb  --AAAB
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1)
union 
select * from tb  --AABBCC
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,11,1) and substring(num,6,1)= substring(num,7,1)
union 
select * from tb  --AAA
where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1)
union 
select * from tb  --AAABBB
where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1) 
and substring(num,6,1)= substring(num,8,1) and substring(num,6,1)= substring(num,7,1)
union 
select * from tb  --AAAA
where len(num)=11 and substring(num,11,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1) and substring(num,8,1)= substring(num,9,1)
union 
select * from tb  --AAAB
where len(num)=11 and substring(num,8,1)= substring(num,9,1) and substring(num,10,1)= substring(num,9,1)

[bluedreams85]

谢谢各位帅哥。

[fpzgm]

[abuying]

[Quote=引用 9 楼 lxpbs8851 的回复:]

这是体力劳动的
[/Quote]
可以使用循环啊

declare @i int
declare @j int
set @i=0
set @j=0

while @i<=9 
 begin
  --abab
  while @j<=9
   begin
    if(@i=0)
     insert into lianghao select '0'+cast(@j as varchar(1))+'0'+cast(@j as varchar(1))
   else
    begin
   if(@i<>@j)
    begin
  insert into lianghao select cast(@i*1000+@j*100+@i*10+@j  as varchar(10))
    end
    end
   set @j=@j+1
  end
set @i=@i+1
 end
--同理

[bluedreams85]

结贴一会给你们转分去。。。

[coleling]

楼主的问题很有意思，下面是我的完整的解决方案

--建表，插演示数据
create table tb(id int identity, code varchar(20))
insert into tb
select 13312348989 union all
select 13312348889 union all
select 13312348899 union all
select 13312349988 union all
select 13312344244 union all
select 13312344333 union all
select 13312333444
go

--建立函数，返回指定位数特征码。如8989返回ABAB
if object_id('GetChar','FN') is not null
	drop function GetChar
go

create function dbo.GetChar(@code varchar(20),@num int)
returns varchar(10)
as
begin
	if @num < 2 or @num > 10 
		return ''

	declare @ret varchar(10)
	declare @t table(row int, num int)
	declare @i int, @j int,@s varchar(20)
	set @i = 2
	set @j = 0
	set @s = right(@code,@num)

	set @ret = 'A'
	insert @t values(1,0)
	while @i <= @num
	begin
		if charindex(substring(@s,@i,1),@s) between 1 and @i-1
		begin
			set @ret = @ret + char(64+charindex(substring(@s,@i,1),@s)-(select num from @t where row = charindex(substring(@s,@i,1),@s)))
			set @j = @j + 1
		end
		else
			set @ret = @ret + char(64-@j+@i)

		insert @t values(@i,@j)		
		set @i = @i + 1
	end

	return @ret
end
--返回所有手机号码的后四位/后五位特征码
select *,'后四位' = dbo.GetChar(code,4),'后五位' = dbo.GetChar(code,5) from tb
/*
id          code                 后四位        后五位
----------- -------------------- ---------- ----------
1           13312348989          ABAB       ABCBC
2           13312348889          AAAB       ABBBC
3           13312348899          AABB       ABBCC
4           13312349988          AABB       ABBCC
5           13312344244          ABAA       AABAA
6           13312344333          ABBB       AABBB
7           13312333444          ABBB       AABBB

(7 行受影响)
*/

--返回后四位特征码是AABB的手机号
select * from tb where dbo.GetChar(code,4) = 'AABB' 
/*
id          code
----------- --------------------
3           13312348899
4           13312349988

(2 行受影响)
*/

[Quote=引用 7 楼 bluedreams85 的回复:]
谁全部帮我写出来我给他1K分。。。
[/Quote]
楼主，记得结贴哦，还有，1K分哦!