今天是周日，给大家看点轻松的——用 SQL 打印九九乘法表。

九九乘法表的 SQL 我曾发布到其它内容平台，现在把 SQL 拷过来，稍微加一些说明。

下图是我从网上找到一个九九乘法表，本文要实现的也是这样的效果。

图1 九九乘法表

打印九九乘法表需要经过下面这几个步骤：

构建一张 1 到 9 的数字表；

生成所有乘法算式的组合；

做行转列处理，格式化输出。

1 构建 1-9 的数字表

如果我们有数字辅助表(有专门的字段存储从0/1起始的自然数列)，那可以直接使用该表。如果没有数字辅助表，找到任意一张存储大于 9 条记录数的表，借助于窗口函数或者用户变量生成 1-9 的自然数列。

窗口函数生成 1-9 的自然数列：

SELECT

ROW_NUMBER() OVER() AS num

FROM

emp

LIMIT 9

使用用户变量生成 1-9 的自然数列。

SELECT

@rn := @rn + 1 AS num

FROM

emp,(SELECT @rn:=0) a

LIMIT 9

由于我的 emp 表的记录数大于 9，所以用上面两种方式构建自然数列没有问题。

如果表的行数一直在变动，也可以使用递归的方式生成自然数列：

WITH RECURSIVE t(num) AS (

SELECT 1 AS num

UNION ALL

SELECT num + 1 FROM t WHERE num < 9

)

SELECT * FROM t

不想太折腾的话，那就自己造数据吧，就像这样：

WITH t AS

(SELECT 1 AS num

UNION SELECT 2

UNION SELECT 3

UNION SELECT 4

UNION SELECT 5

UNION SELECT 6

UNION SELECT 7

UNION SELECT 8

UNION SELECT 9)

SELECT * FROM t

这一步输出的结果就是 1-9 的自然数列。

2 生成所有乘法算式的组合

将 t 表做自关联就可以生成所有乘法算式的组合。

观察图1可知，乘法表由九行九列的乘法算式组成，只是第一行只有一列有算式，第二行有两列算式，......，第九行有九列算式。每个算式的第一个数表示所在的列，第二个数表示所在的行。因此，当 t 表自关联时可将第一个表的字段的值作为算式的第一个数(字段名为 f)，将第二个表的字段的值作为算式的第二个数(字段名为 s)。

t2 AS

(SELECT

a.num AS f,

b.num AS s,

CONCAT(

a.num,

' x ',

b.num,

' = ',

a.num * b.num

) AS r

FROM

t AS a

INNER JOIN t AS b

ON a.num <= b.num)

SELECT

*

FROM

t2

这步输出的结果如下(部分)：

f s r

------ ------ ------------

1 1 1 x 1 = 1

2 2 2 x 2 = 4

1 2 1 x 2 = 2

3 3 3 x 3 = 9

2 3 2 x 3 = 6

1 3 1 x 3 = 3

4 4 4 x 4 = 16

3 4 3 x 4 = 12

2 4 2 x 4 = 8

1 4 1 x 4 = 4

5 5 5 x 5 = 25

4 5 4 x 5 = 20

3 5 3 x 5 = 15

2 5 2 x 5 = 10

1 5 1 x 5 = 5

3 做行转列处理，格式化输出

将步骤 2 中的结果集，字段 f 作为转列的依据，字段 s 作为分组的依据。

SELECT

MAX(IF(f = 1, r, '')) AS v1,

MAX(IF(f = 2, r, '')) AS v2,

MAX(IF(f = 3, r, '')) AS v3,

MAX(IF(f = 4, r, '')) AS v4,

MAX(IF(f = 5, r, '')) AS v5,

MAX(IF(f = 6, r, '')) AS v6,

MAX(IF(f = 7, r, '')) AS v7,

MAX(IF(f = 8, r, '')) AS v8,

MAX(IF(f = 9, r, '')) AS v9

FROM

t2

GROUP BY s

这一步将输出最终的结果。

完整的 SQL 如下：

WITH RECURSIVE t (num) AS

(SELECT

1 AS num

UNION

ALL

SELECT

num + 1

FROM

t

WHERE num < 9),

t2 AS

(SELECT

a.num AS f,

b.num AS s,

CONCAT(

a.num,

' x ',

b.num,

' = ',

a.num * b.num

) AS r

FROM

t AS a

INNER JOIN t AS b

ON a.num <= b.num)

SELECT

MAX(IF(f = 1, r, '')) AS v1,

MAX(IF(f = 2, r, '')) AS v2,

MAX(IF(f = 3, r, '')) AS v3,

MAX(IF(f = 4, r, '')) AS v4,

MAX(IF(f = 5, r, '')) AS v5,

MAX(IF(f = 6, r, '')) AS v6,

MAX(IF(f = 7, r, '')) AS v7,

MAX(IF(f = 8, r, '')) AS v8,

MAX(IF(f = 9, r, '')) AS v9

FROM

t2

GROUP BY s

本文分享自微信公众号 - SQL实现(gh_684ee9235a26)。

如有侵权，请联系 support@oschina.cn 删除。

本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。