一条极简SQL实现九九乘法表

用一条极简单的SQL生成下图所示的九九乘法表

with recursive cte as
(
select  1 as i
union all 
select i+1 as i from cte where i<9
)
select group_concat(concat(b.i,"*",a.i,"=",b.i*a.i)  order by b.i separator " ")  as "九九乘法表" from cte a
left join cte b on b.i <= a.i
group by a.i;
复制

执行结果如下：

如上图结果，3*5 与 3*4 没有对齐 所以稍微改进一下
改进后的代码

with recursive cte as
(
select  1 as i
union all 
select i+1 as i from cte where i<9
)
select group_concat(concat(b.i,"*",a.i,"=",b.i*a.i,case when b.i*a.i <10 then "   " else " "end)  order by b.i separator "  ")  as "九九乘法表" from cte a
left join cte b on b.i <= a.i
group by a.i;
复制

改进后的结果

从上图可以看出都对齐了。