如何实现以下规则的号码分级筛选？
号码源有这些：（这只是一个例子，真实数据可能会更多）
5001,5002,5003,5004,5005,5006,5007,5008,5009,5010,5011,5012,5013,5014,5015,5016,5017,5018,5019,5020,5021,5022,5023,5024,5025,5026,5027,5028,5029,5030,5031,5032,5033,5034,5035,5036,5037,5038,5039,5040,5041,5042,5043,5044,5045,5046,5047,5048,5049,5050,5051,5055,6446,6447,6448,6449,6450,6451,6452,6453,6454,6455,6456,6457,6458,6459,6460,6461,6462,6463,6464,6465,6466,6467,6468,6469,6470,6471,6472,6473,6474,6475,6476,6477,6478,6479,6480,6481,6482,6483,6484,6485,6486,6487,6488,6489,6490,6491,6492,6493,6494,6495,6496,6497,6498,6499,6500,6501,6502,6503,6504,6505,6506,6507,6508,6509,6510,6511,6512,6513,6514,6515,6516,6517,6518,6519,6520,6521,6522,6523,6524,6525,6526,6527,6528,6529,6530,6531,6532,6533,6534,6535,6536,6537,6538,6539,6540,6541,6542,6543,6544,6545,6546,6547,6548,6549,6550,6551,6552,6553,6554,6555,6556,6557,6558,6559,6560,6561,6562,6563,6564,6565,6566,6567,6568,6569,6570,6571,6572,6573,6574,6575,6576,6577,6578,6579,6580,6581,6582,6583,6584,6585,6586,6587,6588,6589,6590,6591,6592,6593,6594,6595,6596,6597,6598,6599,6600
要求按以上号码源分级筛选：
1、 四位同号的设为一级，打印如下：
1111 一级号码
2222 一级号码
3333 一级号码
4444 一级号码
…… ……
2、 四位连号的设为二级，打印如下
1234 二级号码
2345 二级号码
3456 二级号码
9876 二级号码
6543 二级号码
…… ……
3、 三位同号的设为三级，打印如下：
51111 三级号码
62222 三级号码
63333 三级号码
…… ……
4、 两两同号的设为四级，打印如下：
1122 四级号码
2211 四级号码
2255 四级号码
5566 四级号码
…… ……
5、 三位连号的设为五级，打印如下：
5321 五级号码
5123 五级号码
5345 五级号码
4789 五级号码
…… ……
------解决方案--------------------
declare @str varchar(8000)
create table #(id varchar(8))
set @str='5001,5002,5003,5004,5005,5006,5007,5008,5009,5010,5011,5012,5013,5014,5015,5016,5017,5018,5019,5020,5021,5022,5023,5024,5025,5026,5027,5028,5029,5030,5031,5032,5033,5034,5035,5036,5037,5038,5039,5040,5041,5042,5043,5044,5045,5046,5047,5048,5049,5050,5051,5055,6446,6447,6448,6449,6450,6451,6452,6453,6454,6455,6456,6457,6458,6459,6460,6461,6462,6463,6464,6465,6466,6467,6468,6469,6470,6471,6472,6473,6474,6475,6476,6477,6478,6479,6480,6481,6482,6483,6484,6485,6486,6487,6488,6489,6490,6491,6492,6493,6494,6495,6496,6497,6498,6499,6500,6501,6502,6503,6504,6505,6506,6507,6508,6509,6510,6511,6512,6513,6514,6515,6516,6517,6518,6519,6520,6521,6522,6523,6524,6525,6526,6527,6528,6529,6530,6531,6532,6533,6534,6535,6536,6537,6538,6539,6540,6541,6542,6543,6544,6545,6546,6547,6548,6549,6550,6551,6552,6553,6554,6555,6556,6557,6558,6559,6560,6561,6562,6563,6564,6565,6566,6567,6568,6569,6570,6571,6572,6573,6574,6575,6576,6577,6578,6579,6580,6581,6582,6583,6584,6585,6586,6587,6588,6589,6590,6591,6592,6593,6594,6595,6596,6597,6598,6599,6600'
set @str='insert into # select '+replace(@str,',',' union select ')
exec(@str)
--select * from #
select
id,
case
when a=b and a=c and a=d then 1
when (a=b+1 and b=c+1 and c=d+1) or (a=b-1 and b=c-1 and c=d-1) then 2
when (a=b and a=c) or (b=c and c=d) then 3
when (a=b and c=d) then 4
when (a=b+1 and b=c+1) or (b=c+1 and c=d+1) or (a=b-1 and b=c-1) or (b=c-1 and c=d-1) then 5
else 6
end as type
from
(select id,left(id,1) as a,substring(id,2,1) as b,substring(id,3,1) as c,right(id,1) as d from #) t
order by
type,id
drop table #
------解决方案--------------------
- SQL code
declare @a varchar(8000)set @a='5001,5002,5003,5004,5005,5006,5007,5008,5009,5010,5011,5012,5013,5014,5015,5016,5017,5018,5019,5020,5021,5022,5023,5024,5025,5026,5027,5028,5029,5030,5031,5032,5033,5034,5035,5036,5037,5038,5039,5040,5041,5042,5043,5044,5045,5046,5047,5048,5049,5050,5051,5055,6446,6447,6448,6449,6450,6451,6452,6453,6454,6455,6456,6457,6458,6459,6460,6461,6462,6463,6464,6465,6466,6467,6468,6469,6470,6471,6472,6473,6474,6475,6476,6477,6478,6479,6480,6481,6482,6483,6484,6485,6486,6487,6488,6489,6490,6491,6492,6493,6494,6495,6496,6497,6498,6499,6500,6501,6502,6503,6504,6505,6506,6507,6508,6509,6510,6511,6512,6513,6514,6515,6516,6517,6518,6519,6520,6521,6522,6523,6524,6525,6526,6527,6528,6529,6530,6531,6532,6533,6534,6535,6536,6537,6538,6539,6540,6541,6542,6543,6544,6545,6546,6547,6548,6549,6550,6551,6552,6553,6554,6555,6556,6557,6558,6559,6560,6561,6562,6563,6564,6565,6566,6567,6568,6569,6570,6571,6572,6573,6574,6575,6576,6577,6578,6579,6580,6581,6582,6583,6584,6585,6586,6587,6588,6589,6590,6591,6592,6593,6594,6595,6596,6597,6598,6599,6600'declare @s table(a char(4),a1 int,a2 int,a3 int,a4 int,flag varchar(20))declare @t table(id int identity(1,1),x int)set rowcount 10000insert @t select 1 from syscolumns a,syscolumns bset rowcount 0insert @s(a,a1,a2,a3,a4)select aa,cast(left(aa,1) as int),cast(substring(aa,2,1) as int),cast(substring(aa,3,1) as int),cast(right(aa,1) as int)from(select substring(@a+',',id,charindex(',',@a+',',id+1)-id) aa from @t where substring(',[email protected],id,1)=',')ggupdate @s set flag=case when a2=a1 and a3=a1 and a4=a1 then '一级号码' when a1+1=a2 and a2+1=a3 and a3+1=a4 or a1-1=a2 and a2-1=a3 and a3-1=a4 then '二级号码' when a1=a2 and a2=a3 and a3<>a4 or a1<>a2 and a2=a3 and a3=a4 then '三级号码' when a1=a2 and a3=a4 and a2<>a3 then '四级号码' when a1+1=a2 and a2+1=a3 and a3+1<>a4 or a1+1<>a2 and a2+1=a3 and a3+1=a4 or a1+1<>a2 and a2-1=a3 and a3-1=a4 or a1-1=a2 and a2-1=a3 and a3-1<>a4 then '五级号码'endselect * from @s