有一个数据表tb22,以下数据在一个字段里，字段名'NOTEXT'
'03 05 09 15 20 25'
'01 05 13 15 21 25'
'03 05 15 22 24 25'
'05 08 11 13 15 25'
'07 08 15 23 25 32'
'02 13 19 20 26 27'
'04 11 18 19 26 31'
'07 10 17 23 24 32'
.......
......一共有37000条记录
其中有2个尾数相同的、或者是3个尾数、或者4个尾数相同的、或者有两位同号的。
请问
如何把有2个或3个或4个的相同尾数或两位同号的每条数据统计出来？
举例
'22','33' -----首尾数均为1个数，把'22','33'同时有的统计出来
'11','33' -----首尾数均为1个数，把'11','33'同时有的统计出来
'11','22' -----首尾数均为1个数，把'11','22'同时有的统计出来
'01','11','21','31' -----尾数均为1
'02','12','22 32' -----尾数均为2
'03','13','23','33' -----尾数均为3
'04','14','24' ----- 尾数均为4
'05','15','25' ----- 尾数均为5
'06','16','26' ----- 尾数均为6
'07','17','27' ----- 尾数均为7
'08','18','28' ----- 尾数均为8
'09','19','29' ----- 尾数均为9
'10','20','30' ----- 尾数均为0
大侠指点言谢！
相同的尾数统计
------解决方案--------------------
create table #tb(col varchar(100))
insert into #tb
select '03 05 09 15 20 25'
union all select '01 05 13 15 21 25'
union all select '03 05 15 22 24 25'
union all select '05 08 11 13 15 25'
union all select '07 08 15 23 25 32'
union all select '02 13 19 20 26 27'
union all select '04 11 18 19 26 31'
union all select '07 10 17 23 24 32'
go
;with cte as
(
select id=ROW_NUMBER() over(Order by getdate()),col from #tb
),
cte2 as
(
select id,right(ss,1) as No,count(*) as count
from
(select id,substring(col,number,charindex(' ',col+' ',number)-number) as ss
from cte,master..spt_values
where type='P' and number>=1 and number<=len(col) and substring(' '+col,number,1)=' '
)t
group by id,right(ss,1)
having count(*)>=2
union all
select id,a1,count(*)+1 as num
from
(
select id,case when right(ss,1)=left(ss,1) and left(ss,1)<>'0' then left(ss,1) end as a1
from
(
select id,substring(col,number,charindex(' ',col+' ',number)-number) as ss
from cte,master..spt_values
where type='P' and number>=1 and number<=len(col) and substring(' '+col,number,1)=' '
)t
)a
where isnull(a1,'')<>''
group by id,a1
)
select a.col,[0]=max(case when b.No=0 then b.count end),
[1]=max(case when b.No=1 then b.count end),
[2]=max(case when b.No=2 then b.count end),
[3]=max(case when b.No=3 then b.count end),
[4]=max(case when b.No=4 then b.count end),
[5]=max(case when b.No=5 then b.count end),
[6]=max(case when b.No=6 then b.count end),
[7]=max(case when b.No=7 then b.count end),
[8]=max(case when b.No=8 then b.count end),
[9]=max(case when b.No=9 then b.count end)
from cte a
left join cte2 b on a.ID=b.id
where b.count>=2
group by a.col
/*
col 0 1 2 3 4 5 6 7 8 9
01 05 13 15 21 25 NULL 2 NULL NULL NULL 3 NULL NULL NULL NULL
03 05 09 15 20 25 NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL
03 05 15 22 24 25 NULL NULL 2 NULL NULL 3 NULL NULL NULL NULL
04 11 18 19 26 31 NULL 2 NULL NULL NULL NULL NULL NULL NULL NULL
05 08 11 13 15 25 NULL 2 NULL NULL NULL 3 NULL NULL NULL NULL
07 08 15 23 25 32 NULL NULL NULL NULL NULL 2 NULL NULL NULL NULL
07 10 17 23 24 32 NULL NULL NULL NULL NULL NULL NULL 2 NULL NULL
*/
------解决方案--------------------
if object_id('Tempdb..#tb') is not null drop table #tb
--建临时表，
--col为原字符串，字段[0]~[9]为尾数，值为各个尾数的数量
create table #tb(
col varchar(100) null,
[w0] int null,
[w1] int null,
[w2] int null,
[w3] int null,
[w4] int null,
[w5] int null,