把尾数一样的统计出来！
有一个数据表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','2232' -----尾数均为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
/*
col0123456789
01 05 13 15 21 25NULL2NULLNULLNULL3NULLNULLNULLNULL
03 05 09 15 20 25NULLNULLNULLNULLNULL3NULLNULLNULLNULL
03 05 15 22 24 25NULLNULL2NULLNULL3NULLNULLNULLNULL
04 11 18 19 26 31NULL2NULLNULLNULLNULLNULLNULLNULLNULL
05 08 11 13 15 25NULL2NULLNULLNULL3NULLNULLNULLNULL
07 08 15 23 25 32NULLNULLNULLNULLNULL2NULLNULLNULLNULL
07 10 17 23 24 32NULLNULLNULLNULLNULLNULLNULL2NULLNULL
*/
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,
[w6] int null,
[w7] int null,
[w8] int null,
[w9] int null
)
--插入数据
insert into #tb(col)
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
--计算各个尾数的个数
update #tb set [w0]=(case when right(left(col,2),1)='0' then 1 else 0 end
+case when right(left(col,5),1)='0' then 1 else 0 end
+case when right(left(col,8),1)='0' then 1 else 0 end
+case when right(left(col,11),1)='0' then 1 else 0 end
+case when right(left(col,14),1)='0' then 1 else 0 end
+case when right(left(col,17),1)='0' then 1 else 0 end
),
[w1]=(case when right(left(col,2),1)='1' then 1 else 0 end
+case when right(left(col,5),1)='1' then 1 else 0 end
+case when right(left(col,8),1)='1' then 1 else 0 end
+case when right(left(col,11),1)='1' then 1 else 0 end
+case when right(left(col,14),1)='1' then 1 else 0 end
+case when right(left(col,17),1)='1' then 1 else 0 end
),
[w2]=(case when right(left(col,2),1)='2' then 1 else 0 end
+case when right(left(col,5),1)='2' then 1 else 0 end
+case when right(left(col,8),1)='2' then 1 else 0 end
+case when right(left(col,11),1)='2' then 1 else 0 end
+case when right(left(col,14),1)='2' then 1 else 0 end
+case when right(left(col,17),1)='2' then 1 else 0 end
),
[w3]=(case when right(left(col,2),1)='3' then 1 else 0 end
+case when right(left(col,5),1)='3' then 1 else 0 end
+case when right(left(col,8),1)='3' then 1 else 0 end
+case when right(left(col,11),1)='3' then 1 else 0 end
+case when right(left(col,14),1)='3' then 1 else 0 end
+case when right(left(col,17),1)='3' then 1 else 0 end
),
[w4]=(case when right(left(col,2),1)='4' then 1 else 0 end
+case when right(left(col,5),1)='4' then 1 else 0 end
+case when right(left(col,8),1)='4' then 1 else 0 end
+case when right(left(col,11),1)='4' then 1 else 0 end
+case when right(left(col,14),1)='4' then 1 else 0 end
+case when right(left(col,17),1)='4' then 1 else 0 end
),
[w5]=(case when right(left(col,2),1)='5' then 1 else 0 end
+case when right(left(col,5),1)='5' then 1 else 0 end
+case when right(left(col,8),1)='5' then 1 else 0 end
+case when right(left(col,11),1)='5' then 1 else 0 end
+case when right(left(col,14),1)='5' then 1 else 0 end
+case when right(left(col,17),1)='5' then 1 else 0 end
),
[w6]=(case when right(left(col,2),1)='6' then 1 else 0 end
+case when right(left(col,5),1)='6' then 1 else 0 end
+case when right(left(col,8),1)='6' then 1 else 0 end
+case when right(left(col,11),1)='6' then 1 else 0 end
+case when right(left(col,14),1)='6' then 1 else 0 end
+case when right(left(col,17),1)='6' then 1 else 0 end
),
[w7]=(case when right(left(col,2),1)='7' then 1 else 0 end
+case when right(left(col,5),1)='7' then 1 else 0 end
+case when right(left(col,8),1)='7' then 1 else 0 end
+case when right(left(col,11),1)='7' then 1 else 0 end
+case when right(left(col,14),1)='7' then 1 else 0 end
+case when right(left(col,17),1)='7' then 1 else 0 end
),
[w8]=(case when right(left(col,2),1)='8' then 1 else 0 end
+case when right(left(col,5),1)='8' then 1 else 0 end
+case when right(left(col,8),1)='8' then 1 else 0 end
+case when right(left(col,11),1)='8' then 1 else 0 end
+case when right(left(col,14),1)='8' then 1 else 0 end
+case when right(left(col,17),1)='8' then 1 else 0 end
),
[w9]=(case when right(left(col,2),1)='9' then 1 else 0 end
+case when right(left(col,5),1)='9' then 1 else 0 end
+case when right(left(col,8),1)='9' then 1 else 0 end
+case when right(left(col,11),1)='9' then 1 else 0 end
+case when right(left(col,14),1)='9' then 1 else 0 end
+case when right(left(col,17),1)='9' then 1 else 0 end
)
--查看结果
declare @num varchar(10)
declare @col varchar(10)
declare @sql varchar(1000)
set @col='w5'---尾数
set @num='3'---个数
if(@col='')
begin
set @sql='select * from #tb where '+@num
end
else
begin
set @sql='select * from #tb where ['+ @col+']='+@num
end
exec(@sql)
