今天是周日,给大家看点轻松的——用 SQL 打印九九乘法表。
九九乘法表的 SQL 我曾发布到其它内容平台,现在把 SQL 拷过来,稍微加一些说明。
下图是我从网上找到一个九九乘法表,本文要实现的也是这样的效果。
图1 九九乘法表
打印九九乘法表需要经过下面这几个步骤:
如果我们有数字辅助表(有专门的字段存储从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 的自然数列。
将 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
将步骤 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