怎么才能够生成这样的一列呢,这列的数据是下面这样的
00
01
02
03
04
05
06
07
08
09
0A
0B
:
0X
0Y
0Z
10
11
12
:
1Z
20
:
9Z
A0
:
XX
也就是说各位先1到9,再加一的话就是A一直到Z,然后进到十位,相当于不是十进制也不是二进制,而是36进制
00
01
02
03
04
05
06
07
08
09
0A
0B
:
0X
0Y
0Z
10
11
12
:
1Z
20
:
9Z
A0
:
XX
也就是说各位先1到9,再加一的话就是A一直到Z,然后进到十位,相当于不是十进制也不是二进制,而是36进制
insert into #tb values(0)
insert into #tb values(1)
insert into #tb values(2)
insert into #tb values(3)
insert into #tb values(4)
insert into #tb values(5)
insert into #tb values(6)
insert into #tb values(7)
insert into #tb values(8)
insert into #tb values(9)
insert into #tb values('a')
insert into #tb values('b')
insert into #tb values('c')
insert into #tb values('d')
insert into #tb values('e')
insert into #tb values('f')
insert into #tb values('g')
insert into #tb values('h')
insert into #tb values('i')
insert into #tb values('j')
insert into #tb values('k')
insert into #tb values('l')
insert into #tb values('m')
insert into #tb values('n')
insert into #tb values('o')
insert into #tb values('p')
insert into #tb values('q')
insert into #tb values('r')
insert into #tb values('s')
insert into #tb values('t')
insert into #tb values('u')
insert into #tb values('v')
insert into #tb values('w')
insert into #tb values('x')
insert into #tb values('y')
insert into #tb values('z')select *
from (
select a.a1+b.a1 as bb
from #tb a ,#tb b
)t
order by bb
select top 40 *
from (
select a.a1+b.a1 as bb
from #tb a ,#tb b
)t
order by bb00
01
02
03
04
05
06
07
08
09
0a
0b
0c
0d
0e
0f
0g
0h
0i
0j
0k
0l
0m
0n
0o
0p
0q
0r
0s
0t
0u
0v
0w
0x
0y
0z
10
11
12
13
with a1 as
(
select '00' xh
union all
select
cast(
case
when right(xh,1)='Z'
then
case
when left(xh,1)<'9' then cast(left(xh,1)+1 as char(1))
else case when left(xh,1)='9' then 'A' else char(ascii(left(xh,1))+1) end
end+'0'
else
left(xh,1)+
case
when right(xh,1)<'9' then cast(right(xh,1)+1 as char(1))
else case when right(xh,1)='9' then 'A' else char(ascii(right(xh,1))+1) end
end
end as varchar(2))
from a1
where xh<'ZZ'
)
select * FROM a1 OPTION(MAXRECURSION 0)
select SUBSTRING( '0123456789ABCDEF', a.c*4+ b.c,1 ) v
from (
select 0 c union
select 1 union
select 2 union
select 3
) a cross join
(
select 1 c union
select 2 union
select 3 union
select 4 ) b
) e
cross join (
select SUBSTRING( '0123456789ABCDEF', a.c*4+ b.c,1 ) v
from (
select 0 c union
select 1 union
select 2 union
select 3
) a cross join
(
select 1 c union
select 2 union
select 3 union
select 4 ) b ) g order by 1
select top 36 IDENTITY(int, 1,1) AS ID_Num into #a from sys.objects
select SUBSTRING( '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',ID_Num,1) num into #b from #a
select a.num+b.num from #b a cross join #b b order by 1
(
SELECT ch=CHAR(number)
FROM master..spt_values
WHERE type = 'p'
AND (number BETWEEN 48 AND 57 OR number BETWEEN 65 AND 90)
)
SELECT ch=a.ch+b.ch FROM cte a
CROSS JOIN cte b
ORDER BY ch
/*
(1296 行受影响)
*/