我们知道BIGINT的最大值是2^63-1,也就是9,223,372,036,854,775,807,那么2^64是多少呢?再往上呢?穷极无聊的我写了个计算2的N次方的语句,不过超过100后好像速度就慢下来了,用这个语句计算10000次方花了10分钟才出来数。放出300技术分,看看谁写的SQL语句能最快的算出2的10000次方?
SET NOCOUNT ON
GO
USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT PRIMARY KEY,VAL BIGINT)
;WITH MU AS (
SELECT 1 AS ID,1 AS VAL
UNION ALL
SELECT ID+1,0 FROM MU WHERE ID<10000 --10000行预留,大致估计10000次方只需要1000行左右。
)
INSERT INTO TB
SELECT * FROM MU OPTION(MAXRECURSION 0)
DECLARE @I INT ,@COUNT INT,@RESULT VARCHAR(MAX)
SELECT @I=1,@RESULT='',@COUNT=10000 --@COUNT=10000就是计算2的10000次方
WHILE @I<=@COUNT
BEGIN
UPDATE T1 SET T1.VAL=(T1.VAL*2+CASE WHEN T2.VAL>=500000000000000000 THEN 1 ELSE 0 END)%1000000000000000000
FROM TB T1
LEFT JOIN TB T2 ON T1.ID=T2.ID+1
SET @I=@I+1
END
SELECT @RESULT=@RESULT+CONVERT(VARCHAR(20),VAL) FROM TB
WHERE VAL>0
ORDER BY ID DESC
SELECT @RESULT
SET NOCOUNT OFF