2022-05-31 20:31阅读: 83评论: 0推荐: 0

SQL Server-九九乘法表

方法一

select
[1] = case when a>0 then '1*'+ltrim(a)+'='+ltrim(a*1) end,
[2] = case when a>1 then '2*'+ltrim(a)+'='+ltrim(a*2) end,
[3] = case when a>2 then '3*'+ltrim(a)+'='+ltrim(a*3) end,
[4] = case when a>3 then '4*'+ltrim(a)+'='+ltrim(a*4) end,
[5] = case when a>4 then '5*'+ltrim(a)+'='+ltrim(a*5) end,
[6] = case when a>5 then '6*'+ltrim(a)+'='+ltrim(a*6) end,
[7] = case when a>6 then '7*'+ltrim(a)+'='+ltrim(a*7) end,
[8] = case when a>7 then '8*'+ltrim(a)+'='+ltrim(a*8) end,
[9] = case when a>8 then '9*'+ltrim(a)+'='+ltrim(a*9) end
from (select number a
      from master..spt_values
      where type='p' and number between 1 and 9
      ) t
go
/*
select number from master..spt_values
where type='p'
0~2047 共2048个数字
*/

方法二

select
[1] = case when a < 1 then '' else '1*'+cast(a as varchar)+'='+cast(a*1 as varchar) end,
[2] = case when a < 2 then '' else '2*'+cast(a as varchar)+'='+cast(a*2 as varchar) end,
[3] = case when a < 3 then '' else '3*'+cast(a as varchar)+'='+cast(a*3 as varchar) end,
[4] = case when a < 4 then '' else '4*'+cast(a as varchar)+'='+cast(a*4 as varchar) end,
[5] = case when a < 5 then '' else '5*'+cast(a as varchar)+'='+cast(a*5 as varchar) end,
[6] = case when a < 6 then '' else '6*'+cast(a as varchar)+'='+cast(a*6 as varchar) end,
[7] = case when a < 7 then '' else '7*'+cast(a as varchar)+'='+cast(a*7 as varchar) end,
[8] = case when a < 8 then '' else '8*'+cast(a as varchar)+'='+cast(a*8 as varchar) end,
[9] = case when a < 9 then '' else '9*'+cast(a as varchar)+'='+cast(a*9 as varchar) end
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 t
go

方法三

select
'1*'+a+'='+CAST(1*(case when a>=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*(case when a>=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*(case when a>=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*(case when a>=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*(case when a>=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*(case when a>=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*(case when a>=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*(case when a>=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*(case when a>=9Then 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
go

方法四

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)+'*'+cast(@x as varchar)+'='+cast(@x*@y as varchar)+' '
    select @y=@y+1
    end
  print @c+char(10)
  select @x=@x+1
  end
go
/*
char(9)  水平制表符
char(10) 换行
char(13) 回车
*/