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

本文介绍了如何使用T-SQL来生成九九乘法表的三种不同方法,包括循环、子查询和表变量。通过示例代码详细展示了每种方法的实现过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

-----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 AS [1]

,

 

 

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

,

 

 

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

,

 

 

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

,

 

 

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

,

 

 

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

,

 

 

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

,

 

 

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

,

 

 

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

--GROUP BY a

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值