[面试]鸿星尔克面试乘法口诀表

周末去面试做题目的时候用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