日期:2014-05-18  浏览次数:20512 次

来,试试用SQL写乖法口决 100分
SQL code

--SQL写乖法口决
--能写出思路与SQL注释,便于大家理解才好,谢谢!
    1     2      3      4      5      6      7      8      9
    ----- ------ ------ ------ ------ ------ ------ ------ ------
1   1X1=1                                                  
2   1X2=2 2X2=4                                            
3   1X3=3 2X3=6  3X3=9                                     
4   1X4=4 2X4=8  3X4=12 4X4=16                             
5   1X5=5 2X5=10 3X5=15 4X5=20 5X5=25                      
6   1X6=6 2X6=12 3X6=18 4X6=24 5X6=30 6X6=36               
7   1X7=7 2X7=14 3X7=21 4X7=28 5X7=35 6X7=42 7X7=49        
8   1X8=8 2X8=16 3X8=24 4X8=32 5X8=40 6X8=48 7X8=56 8X8=64 
9   1X9=9 2X9=18 3X9=27 4X9=36 5X9=45 6X9=54 7X9=63 8X9=72 9X9=81



------解决方案--------------------
SQL code
declare   @i   smallint,@j   smallint,@str   varchar(100)   
  set   @i=1   
  while   @i<=9   
  begin   
  select   @j=1,@str=''   
  while   @j<=@i   
  select   @str=@str   
  +cast(@j   as   char(1))   
  +'*'+cast(@i   as   char(1))   
  +'='+cast(@i*@j   as   char(2))   
  +space(2)   
  ,@j=@j+1   
  print   @str     
  set   @i=@i+1   
  end

------解决方案--------------------
SQL code
select a.number ,
    [1]=CASE WHEN 1>A.NUMBER THEN '' ELSE '1'+'X'+ltrim(a.number)+'='+ltrim(1*a.number) END,
    [2]=CASE WHEN 2>A.NUMBER THEN '' ELSE '2'+'X'+ltrim(a.number)+'='+ltrim(2*a.number) END,
    [3]=CASE WHEN 3>A.NUMBER THEN '' ELSE '3'+'X'+ltrim(a.number)+'='+ltrim(3*a.number) END,
    [4]=CASE WHEN 4>A.NUMBER THEN '' ELSE '4'+'X'+ltrim(a.number)+'='+ltrim(4*a.number) END,
    [5]=CASE WHEN 5>A.NUMBER THEN '' ELSE '5'+'X'+ltrim(a.number)+'='+ltrim(5*a.number) END,
    [6]=CASE WHEN 6>A.NUMBER THEN '' ELSE '6'+'X'+ltrim(a.number)+'='+ltrim(6*a.number) END,
    [7]=CASE WHEN 7>A.NUMBER THEN '' ELSE '7'+'X'+ltrim(a.number)+'='+ltrim(7*a.number) END,
    [8]=CASE WHEN 8>A.NUMBER THEN '' ELSE '8'+'X'+ltrim(a.number)+'='+ltrim(8*a.number) END,
    [9]=CASE WHEN 9>A.NUMBER THEN '' ELSE '9'+'X'+ltrim(a.number)+'='+ltrim(9*a.number) END

from master..spt_values a
where type='p' and number between 1 and 9 


number      1                           2                           3                           4                           5                           6                           7                           8                           9
----------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- ---------------------------
1           1X1=1                                                                                                                                                                                                                           
2           1X2=2                       2X2=4                                                                                                                                                                                               
3           1X3=3                       2X3=6                       3X3=9                                                                                                                                                                   
4           1X4=4                       2X4=8                       3X4=12                      4X4=16                                                                                                                                      
5           1X5=5                       2X5=10                      3X5=15                      4X5=20                      5X5=25