鸿星尔克面试乘法口诀表

周末去面试做题目的时候用SQL写一个自定义函数写一个乘法口诀表

当时没啥时间好像要吃饭了，那题也就没咋写，主要是一时没想到简便的方法，用程序语言两个FOR循环就OK了，SQL还真不知道咋搞？

其实分析一下很很简单

一个内循环 一个外循环

如果是1 那就是1*1

如果是2 那就是1*2 第二列是2*2

select max(case when a < 1 then '' else '1*'+cast(a as varchar)+'='+cast(a*1 as varchar) end) as [1], max(case when a < 2 then '' else '2*'+cast(a as varchar)+'='+cast(a*2 as varchar) end) as [2], max(case when a < 3 then '' else '3*'+cast(a as varchar)+'='+cast(a*3 as varchar) end) as [3], max(case when a < 4 then '' else '4*'+cast(a as varchar)+'='+cast(a*4 as varchar) end) as [4], max(case when a < 5 then '' else '5*'+cast(a as varchar)+'='+cast(a*5 as varchar) end) as [5], max(case when a < 6 then '' else '6*'+cast(a as varchar)+'='+cast(a*6 as varchar) end) as [6], max(case when a < 7 then '' else '7*'+cast(a as varchar)+'='+cast(a*7 as varchar) end) as [7], max(case when a < 8 then '' else '8*'+cast(a as varchar)+'='+cast(a*8 as varchar) end) as [8], max(case when a < 9 then '' else '9*'+cast(a as varchar)+'='+cast(a*9 as varchar) end) as [9] from ( select 1 as a union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 ) as t1 group by a

下面是我写的一个存储过程，但是如何定位格式？

--============================================= -- Author: <David Gong> -- Create date: <2011-10-08> -- Description: <乘法口诀表> -- ============================================= declare @a int,@b varchar(8000),@c int set @a = 1 set @c = 1 set @b = '' while @a <10 begin set @c=1 while @c<=@a begin set @b=@b+right(str(@c),1)+'*'+ltrim(str(@a))+'='+right(str(@a*@c),2) set @c=@c+1 end set @b=@b+char(10) set @a=@a+1 end print @b

朋友改进的版本：

DECLARE @I INT, @J INT,@C VARCHAR(100),@TEMP VARCHAR(100) SET @I=1 SET @J=1 WHILE @I < 10 BEGIN SET @C='' SET @TEMP='' SET @J=1 WHILE @J<10 BEGIN IF @J>@I BREAK ELSE SET @TEMP=CAST(@J AS VARCHAR)+'*'+CAST(@I AS VARCHAR)+'='+CAST(@J*@I AS VARCHAR) SET @C=@C+@TEMP+SPACE(8-LEN(@TEMP)) SET @J=@J+1 END PRINT @C SET @I=@I+1 END

Oracle 版本

网上找的，收藏先 本人暂时对ORACLE木有研究。。。。

SELECT R1 || '*' || R1 || '=' || R1 * R1 A, DECODE (R2, '', '', R2 || '*' || R1 || '=' || R2 * R1) B, DECODE (R3, '', '', R3 || '*' || R1 || '=' || R3 * R1) C, DECODE (R4, '', '', R4 || '*' || R1 || '=' || R4 * R1) D, DECODE (R5, '', '', R5 || '*' || R1 || '=' || R5 * R1) E, DECODE (R6, '', '', R6 || '*' || R1 || '=' || R6 * R1) F, DECODE (R7, '', '', R7 || '*' || R1 || '=' || R7 * R1) G, DECODE (R8, '', '', R8 || '*' || R1 || '=' || R8 * R1) H, DECODE (R9, '', '', R9 || '*' || R1 || '=' || R9 * R1) I FROM (SELECT LEVEL R1, LAG (LEVEL, 1) OVER (ORDER BY LEVEL) R2, LAG (LEVEL, 2) OVER (ORDER BY LEVEL) R3, LAG (LEVEL, 3) OVER (ORDER BY LEVEL) R4, LAG (LEVEL, 4) OVER (ORDER BY LEVEL) R5, LAG (LEVEL, 5) OVER (ORDER BY LEVEL) R6, LAG (LEVEL, 6) OVER (ORDER BY LEVEL) R7, LAG (LEVEL, 7) OVER (ORDER BY LEVEL) R8, LAG (LEVEL, 8) OVER (ORDER BY LEVEL) R9 FROM DUAL CONNECT BY LEVEL < 10) ORACLE-版本2 SELECT LTRIM (SYS_CONNECT_BY_PATH (ROWNUM || '*' || LV || '=' || RPAD (ROWNUM * LV, 2), ' ' ) ) FROM (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL < 10) WHERE LV = 1 CONNECT BY LV + 1 = PRIOR LV