SQL 分解一个 int 并将每个数字相乘

Question

如果我将以下内容作为 int 1234 并将其分解为 1,2,3,4，我该如何使它们分开并能够将它们相乘，例如 1 × 3、2×5、3×3、4×5 的模式不考虑它们是偶数还是奇数。

Answer 1

这是SQL服务器版本，但应该在其他几个版本中工作。 想法是你拿一个Tally表，一个连续数字的表，然后将其连接到整数的位数，然后进行计算/切割事物。

http://rextester.com/JTUXP51597

;WITH cteBase AS (
SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n)
)
, cteTally AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as N
FROM
cteBase c10
CROSS JOIN cteBase c100
)
, cteValue AS (
SELECT * FROM (VALUES (1234)) t(value)
)
SELECT
v.value
,CAST(SUBSTRING(CAST(v.value AS VARCHAR(100)), t.N, 1) AS INT) as Digit
,CASE t.N % 2 WHEN 0 THEN 5 ELSE 3 END as Multiplier
,SUBSTRING(CAST(v.value AS VARCHAR(100)), t.N, 1) + ' x ' + CASE t.N % 2 WHEN 0 THEN '5' ELSE '3' END as Equation
,CAST(SUBSTRING(CAST(v.value AS VARCHAR(100)), t.N, 1) AS INT) * CASE t.N % 2 WHEN 0 THEN 5 ELSE 3 END as Result
FROM
cteValue v
INNER JOIN cteTally t
ON LEN(CAST(v.value AS VARCHAR(100))) >= t.N