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 #
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 #
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 10000
insert @t select 1 from syscolumns a,syscolumns b
set 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(','+@a,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