# MySQL 通过sql输出99乘法表

-- 用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)

• 点赞
• 评论
• 分享
x

海报分享

扫一扫，分享海报

• 收藏
• 手机看

分享到微信朋友圈

x

扫一扫，手机阅读

• 打赏

打赏

只是甲

你的鼓励将是我创作的最大动力

C币 余额
2C币 4C币 6C币 10C币 20C币 50C币
• 一键三连

点赞Mark关注该博主, 随时了解TA的最新博文
03-09 115

07-30 4万+
06-16 1万+
09-14 2562
09-14 5870
03-23 2481
03-20 1万+
01-03 247
07-06
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客