如何求倒序数和同尾数的并集？
例如在一个字段[NO]里有记录
select '01 07 10 23 28 32'
union all select '08 13 17 21 23 32'
union all select '11 17 28 30 31 33'
union all select '08 13 15 26 29 31'
union all select '03 05 23 24 27 32'
union all select '01 12 13 23 30 31'
union all select '06 13 16 21 28 31'
union all select '09 11 16 28 32 33'
union all select '02 03 05 06 18 30'
union all select '05 11 12 13 27 31'
union all select '09 11 12 14 22 33'
union all select '16 17 19 22 31 33'
union all select '03 10 21 22 24 33'
union all select '04 12 18 20 23 32'
union all select '01 07 10 22 32 33'
union all select '05 10 11 23 24 32'
union all select '03 10 14 19 20 30'
union all select '01 13 21 23 25 32'
union all select '07 15 16 22 23 32'
union all select '05 21 23 25 28 32'
union all select '02 08 11 13 24 31'
union all select '10 12 21 22 30 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语句完成出现倒序数或尾数有相同的并集查询并显示如下图
并集，倒序数，同尾数
[解决办法]
--上面的复制少了
if object_id('Tempdb..#tb') is not null drop table #tb
--建临时表，
--col为原字符串，字段[w0]~[w9]为尾数，值为各个尾数的数量
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 '01 07 10 23 28 32'
union all select '08 13 17 21 23 32'
union all select '11 17 28 30 31 33'
union all select '08 13 15 26 29 31'
union all select '03 05 23 24 27 32'
union all select '01 12 13 23 30 31'
union all select '06 13 16 21 28 31'
union all select '09 11 16 28 32 33'
union all select '02 03 05 06 18 30'
union all select '05 11 12 13 27 31'
union all select '09 11 12 14 22 33'
union all select '16 17 19 22 31 33'
union all select '03 10 21 22 24 33'
union all select '04 12 18 20 23 32'
union all select '01 07 10 22 32 33'
union all select '05 10 11 23 24 32'
union all select '03 10 14 19 20 30'
union all select '01 13 21 23 25 32'
union all select '07 15 16 22 23 32'
union all select '05 21 23 25 28 32'
union all select '02 08 11 13 24 31'
union all select '10 12 21 22 30 33'
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 int---尾数
declare @stra varchar(10)
declare @strb varchar(10)
declare @strc varchar(10)
declare @strd varchar(10)
declare @stre varchar(10)
declare @strf varchar(10)
declare @strg varchar(10)
set @num=2
set @stra='01,10'
set @strb='02,20'
set @strc='03,30'
set @strd='12,21'
set @stre='23,32'
set @strf='13,31'
set @strg='11,22,33'
select *,(case when charindex(left(@stra,2),col)>0 and charindex(right(@stra,2),col)>0 then @stra+',' else '' end
+case when charindex(left(@strb,2),col)>0 and charindex(right(@strb,2),col)>0 then @strb+',' else '' end
+case when charindex(left(@strc,2),col)>0 and charindex(right(@strc,2),col)>0 then @strc+',' else '' end
+case when charindex(left(@strd,2),col)>0 and charindex(right(@strd,2),col)>0 then @strd+',' else '' end
+case when charindex(left(@stre,2),col)>0 and charindex(right(@stre,2),col)>0 then @stre+',' else '' end
+case when charindex(left(@strf,2),col)>0 and charindex(right(@strf,2),col)>0 then @strf+',' else '' end
+case when charindex(left(@strg,2),col)>0 and charindex(right(left(@strg,5),2),col)>0 and charindex(right(@strg,2),col)>0 then @strg else '' end
)
N'倒序数'
from #tb
where w0=@num
or w1=@num
or w2=@num
or w3=@num
or w4=@num
or w5=@num
or w6=@num
or w7=@num
or w8=@num
or w9=@num
create table u01
(IDD int,[NO] varchar(20))
insert into u01(IDD,[NO])
select 1,'01 07 10 23 28 32'
union all select 2,'08 13 17 21 23 32'
union all select 3,'11 17 28 30 31 33'
union all select 4,'08 13 15 26 29 31'
union all select 5,'03 05 23 24 27 32'
union all select 6,'01 12 13 23 30 31'
union all select 7,'06 13 16 21 28 31'
union all select 8,'09 11 16 28 32 33'
union all select 9,'02 03 05 06 18 30'
union all select 10,'05 11 12 13 27 31'
union all select 11,'09 11 12 14 22 33'
union all select 12,'16 17 19 22 31 33'
union all select 13,'03 10 21 22 24 33'
union all select 14,'04 12 18 20 23 32'
union all select 15,'01 07 10 22 32 33'
union all select 16,'05 10 11 23 24 32'
union all select 17,'03 10 14 19 20 30'
union all select 18,'01 13 21 23 25 32'
union all select 19,'07 15 16 22 23 32'
union all select 20,'05 21 23 25 28 32'
union all select 21,'02 08 11 13 24 31'
union all select 22,'10 12 21 22 30 33'
declare @tsql varchar(8000),@tsql2 varchar(8000)
select @tsql=isnull(@tsql+',','')
+'case when sum(case when right(NOS,1)='''+rtrim(number)+''' then 1 else 0 end)>=2 then '
+'rtrim(sum(case when right(NOS,1)='''+rtrim(number)+''' then 1 else 0 end)) else '''' end '
+''''+rtrim(number)+''' '
from master.dbo.spt_values
where type='P' and number between 0 and 9
select @tsql2=isnull(@tsql2+',','')
+' isnull((select z from (select row_number() over(order by getdate()) ''rn'',z
from d where charindex(x,a.[NO],1)>0 and charindex(y,a.[NO],1)>0) x
where rn='+rtrim(number)+'),'''') '
+'''倒序数'' '
from master.dbo.spt_values
where type='P' and number between 1 and 3
select @tsql='with t as'
+' (select a.IDD,a.[NO],
substring(a.[NO],b.number,charindex('' '',a.[NO]+'' '',b.number)-b.number) ''NOS''
from u01 a
inner join master.dbo.spt_values b
on b.type=''P'' and b.number between 1 and len(a.[NO])
and substring('' ''+a.[NO],b.number,1)='' ''), '
+' d as'
+' (select ''01,10'' z,''01'' x,''10'' y union all '
+' select ''02,20'' z,''02'' x,''20'' y union all '
+' select ''03,30'' z,''03'' x,''30'' y union all '
+' select ''12,21'' z,''12'' x,''21'' y union all '
+' select ''23,32'' z,''23'' x,''32'' y union all '
+' select ''13,31'' z,''13'' x,''31'' y union all '
+' select ''11'' z,''11'' x,''11'' y union all '
+' select ''22'' z,''22'' x,''22'' y union all '
+' select ''33'' z,''33'' x,''33'' y) '
+'select a.IDD,a.[NO],'+@tsql+','+@tsql2
+' from t a '
+' group by a.IDD,a.[NO] '
+' order by a.IDD '
exec(@tsql)
/*
IDD NO 0 1 2 3 4 5 6 7 8 9 倒序数1 倒序数2 倒序数3
----------- -------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---------- ---------- ----------
1 01 07 10 23 28 32 01,10 23,32
2 08 13 17 21 23 32 2 23,32
3 11 17 28 30 31 33 2 11 33
4 08 13 15 26 29 31 13,31
5 03 05 23 24 27 32 2 23,32
6 01 12 13 23 30 31 2 2 13,31
7 06 13 16 21 28 31 2 2 13,31
8 09 11 16 28 32 33 11 33
9 02 03 05 06 18 30 03,30
10 05 11 12 13 27 31 2 13,31 11
11 09 11 12 14 22 33 2 11 22 33
12 16 17 19 22 31 33 22 33
13 03 10 21 22 24 33 2 22 33
14 04 12 18 20 23 32 2 23,32
15 01 07 10 22 32 33 2 01,10 22 33
16 05 10 11 23 24 32 23,32 11
17 03 10 14 19 20 30 3 03,30
18 01 13 21 23 25 32 2 2 23,32
19 07 15 16 22 23 32 2 23,32 22
20 05 21 23 25 28 32 2 23,32
21 02 08 11 13 24 31 2 13,31 11
22 10 12 21 22 30 33 2 2 12,21 22 33
(22 row(s) affected)
*/
