select a.aaa + b.aaa from (select char(number) as aaa from master..spt_values where type = 'p' and ((number between 48 and 57) or (number between 65 and 90))) a ,(select char(number) as aaa from master..spt_values where type = 'p' and ((number between 48 and 57) or (number between 65 and 90))) b order by a.aaa + b.aaa/* 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D . . . ZK ZL ZM ZN ZO ZP ZQ ZR ZS ZT ZU ZV ZW ZX ZY ZZ(1296 行受影响) */
select flag=A+B from (select A=(char(number)) from spt_values where type='p' and patindex('%[a-zA-A0-9]%',char(number))>0)A, (select B=(char(number)) from spt_values where type='p' and patindex('%[a-zA-A0-9]%',char(number))>0)B
从 00-99-A0-A9-AA-B0-BB--------ZZ ==== 99之后为啥不是9A而是A0? AA之后为啥不是AB而是B0? 什么规律?是想要:00,...,0Z,10,...,1Z,... ...,Z0,...,ZZ 还是要:00,10,11,20,21,22,... ...,A0,...,AA,... ...,Z0,...,ZZ前者是:(0到Z) a CROSS JOIN (0到Z) b 后者是:(0到Z) a INNER JOIN (0到Z) b ON a.char <= b.char
--第1个 ;WITH CTE AS( SELECT ch = CHAR(n) FROM dbo.Nums WHERE n BETWEEN 0x30 AND 0x39 OR n BETWEEN 0x41 AND 0x5a ) SELECT code = a.ch + b.ch FROM CTE a CROSS JOIN CTE b --第2个 ;WITH CTE AS( SELECT ch = CHAR(n) FROM dbo.Nums WHERE n BETWEEN 0x30 AND 0x39 OR n BETWEEN 0x41 AND 0x5a ) SELECT code = a.ch + b.ch FROM CTE a INNER JOIN CTE b ON a.ch <= b.ch
00
01
02
--
99
A0
A1
--
A9
--
AA
--
zz
select
a.aaa + b.aaa
from
(select char(number) as aaa from master..spt_values where type = 'p' and ((number between 48 and 57) or (number between 65 and 90))) a
,(select char(number) as aaa from master..spt_values where type = 'p' and ((number between 48 and 57) or (number between 65 and 90))) b
order by a.aaa + b.aaa/*
00
01
02
03
04
05
06
07
08
09
0A
0B
0C
0D
.
.
.
ZK
ZL
ZM
ZN
ZO
ZP
ZQ
ZR
ZS
ZT
ZU
ZV
ZW
ZX
ZY
ZZ(1296 行受影响)
*/
and patindex('%[a-zA-A0-9]%',char(number))>0)A,
(select B=(char(number)) from spt_values where type='p'
and patindex('%[a-zA-A0-9]%',char(number))>0)B
====
99之后为啥不是9A而是A0?
AA之后为啥不是AB而是B0?
什么规律?是想要:00,...,0Z,10,...,1Z,... ...,Z0,...,ZZ
还是要:00,10,11,20,21,22,... ...,A0,...,AA,... ...,Z0,...,ZZ前者是:(0到Z) a CROSS JOIN (0到Z) b
后者是:(0到Z) a INNER JOIN (0到Z) b ON a.char <= b.char
这是ascii码表 对应需要的 用LS的方法吧
;WITH CTE AS(
SELECT ch = CHAR(n) FROM dbo.Nums
WHERE n BETWEEN 0x30 AND 0x39
OR n BETWEEN 0x41 AND 0x5a
)
SELECT code = a.ch + b.ch
FROM CTE a
CROSS JOIN CTE b
--第2个
;WITH CTE AS(
SELECT ch = CHAR(n) FROM dbo.Nums
WHERE n BETWEEN 0x30 AND 0x39
OR n BETWEEN 0x41 AND 0x5a
)
SELECT code = a.ch + b.ch
FROM CTE a
INNER JOIN CTE b
ON a.ch <= b.ch