一句SQL完成乘法表

在论坛上看到,以前很喜欢写SQL，收藏下来重温SQL时看看：

 

SQL  code declare @x int = 1 , @y int , @c varchar ( 6000 ) while ( @x <= 9 ) begin select @y = 1 , @c = '' while ( @y <= @x ) begin select @c = @c + cast ( @y as varchar ) + ' x ' + cast ( @x as varchar ) + ' = ' + cast ( @x * @y as varchar ) + ' ' select @y = @y + 1 end print @c + char ( 10 ) select @x = @x + 1 end /* 1x1=1 1x2=2 2x2=4 1x3=3 2x3=6 3x3=9 1x4=4 2x4=8 3x4=12 4x4=16 1x5=5 2x5=10 3x5=15 4x5=20 5x5=25 1x6=6 2x6=12 3x6=18 4x6=24 5x6=30 6x6=36 1x7=7 2x7=14 3x7=21 4x7=28 5x7=35 6x7=42 7x7=49 1x8=8 2x8=16 3x8=24 4x8=32 5x8=40 6x8=48 7x8=56 8x8=64 1x9=9 2x9=18 3x9=27 4x9=36 5x9=45 6x9=54 7x9=63 8x9=72 9x9=81 */

  SQL code

  
  

   
   

   
   declare
   
    
   
   @x
   
    
   
   int
   
   

   
   declare
   
    
   
   @y
   
    
   
   int
   
   

   
   declare
   
    
   
   @c
   
    
   
   varchar
   
   (
   
   6000
   
   )

   
   set
   
    
   
   @x
   
    
   
   =
   
    
   
   1
   
   

   
   while
   
   (
   
   @x
   
   <=
   
   9
   
   )

   
   begin
   
   
 
   
   select
   
    
   
   @y
   
   =
   
   1
   
   ,
   
   @c
   
   =
   
   ''
   
   
 
   
   while
   
   (
   
   @y
   
   <=
   
   @x
   
   )
 
   
   begin
   
   
   
   
   select
   
    
   
   @c
   
   =
   
   @c
   
   +
   
   cast
   
   (
   
   @y
   
    
   
   as
   
    
   
   varchar
   
   )
   
   +
   
   '
   
   x
   
   '
   
   +
   
   cast
   
   (
   
   @x
   
    
   
   as
   
    
   
   varchar
   
   )
   
   +
   
   '
   
   =
   
   '
   
   
        
   
   +
   
   (
   
   case
   
    
   
   when
   
    
   
   len
   
   (
   
   ltrim
   
   (
   
   @x
   
   *
   
   @y
   
   ))
   
   >
   
   1
   
    
   
   then
   
    
   
   ''
   
    
   
   else
   
    
   
   '
   
    
   
   '
   
    
   
   end
   
   )
   
   +
   
   ltrim
   
   (
   
   @x
   
   *
   
   @y
   
   )
   
   +
   
   '
   
    
   
   '
   
   
   
   
   select
   
    
   
   @y
   
   =
   
   @y
   
   +
   
   1
   
   
 
   
   end
   
   
 
   
   print
   
    
   
   @c
   
   +
   
   char
   
   (
   
   10
   
   )
 
   
   select
   
    
   
   @x
   
   =
   
   @x
   
   +
   
   1
   
   

   
   end
   
   


  
  

 

SQL code

  
  

   
   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
  
  

 SQL code
--这样会不会好看一些
select
max(casewhen a>0then'1*'+a+'='+ltrim(a*1)end)[1],
max(casewhen a>1then'2*'+a+'='+ltrim(a*2)end)[2],
max(casewhen a>2then'3*'+a+'='+ltrim(a*3)end)[3],
max(casewhen a>3then'4*'+a+'='+ltrim(a*4)end)[4],
max(casewhen a>4then'5*'+a+'='+ltrim(a*5)end)[5],
max(casewhen a>5then'6*'+a+'='+ltrim(a*6)end)[6],
max(casewhen a>6then'7*'+a+'='+ltrim(a*7)end)[7],
max(casewhen a>7then'8*'+a+'='+ltrim(a*8)end)[8],
max(casewhen a>8then'9*'+a+'='+ltrim(a*9)end)[9]
from ( select ltrim(number) afrom master..spt_values
    where type='p'andnumber between1and9
) t group by a

SQL code

  
  

   
   

   
   select
   
    

   
   '
   
   1*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   1
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   1
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   1
   
   ]
   
   ,

   
   '
   
   2*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   2
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   2
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   2
   
   ]
   
   ,

   
   '
   
   3*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   3
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   3
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   3
   
   ]
   
   ,

   
   '
   
   4*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   4
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   4
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   4
   
   ]
   
   ,

   
   '
   
   5*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   5
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   5
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   5
   
   ]
   
   ,

   
   '
   
   6*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   6
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   6
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   6
   
   ]
   
   ,

   
   '
   
   7*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   7
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   7
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   7
   
   ]
   
   ,

   
   '
   
   8*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   8
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   8
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   8
   
   ]
   
   ,

   
   '
   
   9*
   
   '
   
   +
   
   a
   
   +
   
   '
   
   =
   
   '
   
   +
   
   CAST
   
   (
   
   9
   
   *
   
   1
   
   *
   
   (
   
   case
   
    
   
   when
   
    a
   
   >=
   
   9
   
   Then
   
    a 
   
   else
   
    
   
   NULL
   
    
   
   end
   
   ) 
   
   as
   
    
   
   varchar
   
   ) 
   
   as
   
    
   
   [
   
   9
   
   ]
   
   

   
   from
   
    (
   
   select
   
    
   
   top
   
    
   
   9
   
    
   
   CAST
   
   (Row_Number()
   
   over
   
    (
   
   order
   
    
   
   by
   
    name) 
   
   as
   
    
   
   varchar
   
   (
   
   10
   
   ) ) 
   
   as
   
    a 
   
   from
   
    master..spt_values )b
 

  
  

 

SQL code

declare @a smallint,
        @b smallint,
        @str varchar(1000)
set @a=1
while @a<=9
begin
    set @b=1
    set @str=''
    while @b<=@a
    begin
        select @str=@str+convert(varchar(1),@b)+'*'+convert(varchar(1),@a)+'='+convert(varchar(2),@a*@b)+space(2)
        set @b=@b+1
    end
    print @str
    set @a=@a+1
end