有一个数据表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大侠指点言谢!相同的尾数统计
'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'select id=identity(int,1,1),col
into #temp
from #tb
select id as 记录号,a1 as 尾号,num as 个数
from
(
select id,right(ss,1) as a1,count(*) as num
from
(
select id,substring(col,number,charindex(' ',col+' ',number)-number) as ss
from #temp,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 #temp,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
)m
order by id,a1/*
记录号 尾号 个数
---------------------
1 5 3
2 1 2
2 5 3
3 2 2
3 5 3
4 1 2
4 5 3
5 5 2
7 1 2
7 1 2
8 7 2*/
当我选择num>=2时,有哪些NOTEXT,并且显示出来
如果当我选择num>=3时,有哪些NOTEXT,并且显示出来
如果当我选择num>=4时,有哪些NOTEXT,并且显示出来
id col .............尾号. .个数. . 尾号. .个数
1 03 05 09 15 20 25 5 3
2 01 05 13 15 21 25 1 2 5 3
3 03 05 15 22 24 25 2 2 5 3
4 05 08 11 13 15 25 1 2 5 3
5 07 08 15 23 25 32 5 2
6 02 13 19 20 26 27
7 04 11 18 19 26 31 1 2 1 2
8 07 10 17 23 24 32 7 2 当‘num’=3时,如何显示如下
id col. . 尾号. . 个数. .尾号. .个数
2 01 05 13 15 21 25 1 2 5 3
3 03 05 15 22 24 25 2 2 5 3
4 05 08 11 13 15 25 1 2 5 3
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';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(*)>=2union 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,b.No,b.count
from cte a
left join cte2 b on a.ID=b.id
where b.count>=2
/*
col No count
01 05 13 15 21 25 1 2
04 11 18 19 26 31 1 2
03 05 09 15 20 25 5 3
01 05 13 15 21 25 5 3
03 05 15 22 24 25 5 3
05 08 11 13 15 25 5 3
07 08 15 23 25 32 5 2
07 10 17 23 24 32 7 2
05 08 11 13 15 25 1 2
04 11 18 19 26 31 1 2
03 05 15 22 24 25 2 2
*/
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(*)>=2union 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,
[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
endexec(@sql)
-----结果(8 行受影响)(8 行受影响)
col w0 w1 w2 w3 w4 w5 w6 w7 w8 w9
---------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
03 05 09 15 20 25 1 0 0 1 0 3 0 0 0 1
01 05 13 15 21 25 0 2 0 1 0 3 0 0 0 0
03 05 15 22 24 25 0 0 1 1 1 3 0 0 0 0
05 08 11 13 15 25 0 1 0 1 0 3 0 0 1 0(4 行受影响)
例如在一个字段[NO]里有记录
'01 07 10 23 28 32'
'08 13 17 21 23 32'
'11 17 28 30 31 33'
'08 13 15 26 29 31'
'03 05 23 24 27 32'
'01 12 13 23 30 31'
'06 13 16 21 28 31'
'09 11 16 28 32 33'
'02 03 05 06 18 30'
'05 11 12 13 27 31'
'09 11 12 14 22 33'
'16 17 19 22 31 33'
'03 10 21 22 24 33'
'04 12 18 20 23 32'
'01 07 10 22 32 33'
'05 10 11 23 24 32'
'03 10 14 19 20 30'
'01 13 21 23 25 32'
'07 15 16 22 23 32'
'05 21 23 25 28 32'
'02 08 11 13 24 31'
'10 12 21 22 30 33'当出现以下倒序数时
'01','10'','
'02','20'','
'03','30'','
'12','21'','
'23','32'','
'13','31'
'11','22','33'如何把这些【NO】显示出来
'01','10'
OR '02','20'
OR '03','30'
OR '12','21'
OR '23','32'
OR '13','31'
OR '11','22','33’
‘OR’以下特征的数据时【尾数有相同的】
'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出现以上特征的数据,均需用sql语句完成出现倒序数或尾数有相同的并集查询并显示