使用T-SQL生成九九乘法表

-----1.使用循环-----------------------------
declare @i int,@j int
declare @MultiTable varchar(4000)

select @i=9,@j=1,@MultiTable=''

while @i>=1
begin
    set @j=@i
    while @j>=1
    begin
        select @MultiTable=convert(char(2),@j)+'* '+convert(char(2),@i)+'= '+convert(char(2),@i*@j)+'    '+@MultiTable
    set @j=@j-1
    end
set @MultiTable=char(13)+@MultiTable
set @i=@i-1
end
print @MultiTable

 

------2.使用子查询------------------------

select
case when I>=1 then convert(char(2),1)+'*'+convert(char(2),I)+' = '+convert(char(2),I) else '' end,
case when I>=2 then convert(char(2),2)+'*'+convert(char(2),I)+' = '+convert(char(2),I*2) else '' end,
case when I>=3 then convert(char(2),3)+'*'+convert(char(2),I)+' = '+convert(char(2),I*3) else '' end,
case when I>=4 then convert(char(2),4)+'*'+convert(char(2),I)+' = '+convert(char(2),I*4) else '' end,
case when I>=5 then convert(char(2),5)+'*'+convert(char(2),I)+' = '+convert(char(2),I*5) else '' end,
case when I>=6 then convert(char(2),6)+'*'+convert(char(2),I)+' = '+convert(char(2),I*6) else '' end,
case when I>=7 then convert(char(2),7)+'*'+convert(char(2),I)+' = '+convert(char(2),I*7) else '' end,
case when I>=8 then convert(char(2),8)+'*'+convert(char(2),I)+' = '+convert(char(2),I*8) else '' end,
case when I>=9 then convert(char(2),9)+'*'+convert(char(2),I)+' = '+convert(char(2),I*9) else '' end
from
(select 1 I
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
) I

go
-----3.使用表变量---------------------
declare @I table
(I tinyint)

declare @J table
(J1 tinyint,J2 tinyint,J3 tinyint,J4 tinyint,J5 tinyint,J6 tinyint,J7 tinyint,J8 tinyint,J9 tinyint)

insert into @I
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9

insert into @J
select 1,2,3,4,5,6,7,8,9

select
convert(char(2),J1)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J1),
case when I>=J2 then convert(char(2),J2)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J2) else '' end,
case when I>=J3 then convert(char(2),J3)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J3) else '' end,
case when I>=J4 then convert(char(2),J4)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J4) else '' end,
case when I>=J5 then convert(char(2),J5)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J5) else '' end,
case when I>=J6 then convert(char(2),J6)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J6) else '' end,
case when I>=J7 then convert(char(2),J7)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J7) else '' end,
case when I>=J8 then convert(char(2),J8)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J8) else '' end,
case when I>=J9 then convert(char(2),J9)+'*'+convert(char(2),I)+' = '+convert(char(2),I*J9) else '' end
from @I ,@J

 

 

--第4种方法改自网上

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

--MAX(CASE WHEN a < 1 THEN '' ELSE '1*'+CAST(a AS VARCHAR)+'='+CAST(a*1 AS VARCHAR) END) AS [1]

SELECT

 

 

CASE WHEN a < 1 THEN '' ELSE '1*' + CAST ( a AS VARCHAR )+ '=' + CAST ( a * 1 AS VARCHAR ) END