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) 回车 */