MySQLはsqlで99乗算表を出力します
3496 ワード
備考:テストデータベースバージョンは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)