MySQL 通过sql输出99乘法表

备注:测试数据库版本为MySQL 8.0

代码

-- 用with递归构造1-9的数据
with RECURSIVE c(n) as
 (select 1 n  union all select n + 1 from c where n < 9),
tmp1 as
(
SELECT c1.n n1,c2.n n2,CONCAT(c1.n,' * ',c2.n,' = ',c1.n*c2.n) exp
from c c1
inner join c c2
on c1.n <= c2.n
)
select max(case when n1 = 1 then exp else null end) as A1,
       max(case when n1 = 2 then exp else null end) as A2,
       max(case when n1 = 3 then exp else null end) as A3,
       max(case when n1 = 4 then exp else null end) as A4,
       max(case when n1 = 5 then exp else null end) as A5,
       max(case when n1 = 6 then exp else null end) as A6,
       max(case when n1 = 7 then exp else null end) as A7,
       max(case when n1 = 8 then exp else null end) as A8,
       max(case when n1 = 9 then exp else null end) as A9
from tmp1
group by n2
order by n2
复制

测试记录

mysql> -- 用with递归构造1-9的数据
mysql> with RECURSIVE c(n) as
    ->  (select 1 n  union all select n + 1 from c where n < 9),
    -> tmp1 as
    -> (
    -> SELECT c1.n n1,c2.n n2,CONCAT(c1.n,' * ',c2.n,' = ',c1.n*c2.n) exp
    -> from c c1
    -> inner join c c2
    -> on c1.n <= c2.n
    -> )
    -> select max(case when n1 = 1 then exp else null end) as A1,
    ->        max(case when n1 = 2 then exp else null end) as A2,
    ->        max(case when n1 = 3 then exp else null end) as A3,
    ->        max(case when n1 = 4 then exp else null end) as A4,
    ->        max(case when n1 = 5 then exp else null end) as A5,
    ->        max(case when n1 = 6 then exp else null end) as A6,
    ->        max(case when n1 = 7 then exp else null end) as A7,
    ->        max(case when n1 = 8 then exp else null end) as A8,
    ->        max(case when n1 = 9 then exp else null end) as A9
    -> from tmp1
    -> group by n2
    -> order by n2;
+-----------+------------+------------+------------+------------+------------+------------+------------+------------+
| A1        | A2         | A3         | A4         | A5         | A6         | A7         | A8         | A9         |
+-----------+------------+------------+------------+------------+------------+------------+------------+------------+
| 1 * 1 = 1 | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
| 1 * 2 = 2 | 2 * 2 = 4  | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
| 1 * 3 = 3 | 2 * 3 = 6  | 3 * 3 = 9  | NULL       | NULL       | NULL       | NULL       | NULL       | NULL       |
| 1 * 4 = 4 | 2 * 4 = 8  | 3 * 4 = 12 | 4 * 4 = 16 | NULL       | NULL       | NULL       | NULL       | NULL       |
| 1 * 5 = 5 | 2 * 5 = 10 | 3 * 5 = 15 | 4 * 5 = 20 | 5 * 5 = 25 | NULL       | NULL       | NULL       | NULL       |
| 1 * 6 = 6 | 2 * 6 = 12 | 3 * 6 = 18 | 4 * 6 = 24 | 5 * 6 = 30 | 6 * 6 = 36 | NULL       | NULL       | NULL       |
| 1 * 7 = 7 | 2 * 7 = 14 | 3 * 7 = 21 | 4 * 7 = 28 | 5 * 7 = 35 | 6 * 7 = 42 | 7 * 7 = 49 | NULL       | NULL       |
| 1 * 8 = 8 | 2 * 8 = 16 | 3 * 8 = 24 | 4 * 8 = 32 | 5 * 8 = 40 | 6 * 8 = 48 | 7 * 8 = 56 | 8 * 8 = 64 | NULL       |
| 1 * 9 = 9 | 2 * 9 = 18 | 3 * 9 = 27 | 4 * 9 = 36 | 5 * 9 = 45 | 6 * 9 = 54 | 7 * 9 = 63 | 8 * 9 = 72 | 9 * 9 = 81 |
+-----------+------------+------------+------------+------------+------------+------------+------------+------------+
9 rows in set (0.00 sec)
复制