SQL SERVER实现乘法口诀表

来源：互联网发布：买不起车房知乎编辑：程序博客网时间：2024/06/05 10:42

看到群里有人说SQL怎么实现乘法口诀表，所以想了想写出的脚本

具体思路是把乘法口诀的计算结果列出来，然后使用动态行转列实现，方法可能很笨，请谅解

SELECT 'X'+cast(A.NUMBER AS VARCHAR(10)) X,'Y'+cast(B.NUMBER AS VARCHAR(10)) Y,CAST(A.NUMBER AS VARCHAR(10))+'*'+CAST((CASE WHEN B.NUMBER<A.NUMBER THEN NULL ELSE B.NUMBER END) AS VARCHAR(10))+'='+ CAST(A.NUMBER*(CASE WHEN B.NUMBER<A.NUMBER THEN NULL ELSE B.NUMBER END) AS VARCHAR(10)) C INTO #1
FROM (SELECT NUMBER FROM [master]..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 9) A
LEFT JOIN (SELECT NUMBER FROM [master]..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 9) B ON A.NUMBER<B.NUMBER+1

declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + X from #1 group by X
exec ('select * from #1 a pivot (MAX(C) for X in (' + @sql + ')) b')

0 0