# 一个sql输出乘法口诀

itpub上的输出结果，我的写法如下：

SQL> with t as
2   (select rownum r from dual connect by rownum < 10)
3  select substr(listagg(s, ' ') within group(order by s),
4                1,
5                case
6                  when a = 1 then
7                   5
8                  else
9                   instr(listagg(s, ' ') within group(order by s), a * a) + 1
10                end)
11    from (select t2.r || '*' || t1.r || '=' || t1.r * t2.r s, t1.r a
12            from t t1, t t2)
13   group by a
14  ;

SUBSTR(LISTAGG(S,'')WITHINGROU
--------------------------------------------------------------------------------
1*1=1
1*2=2 2*2=4
1*3=3 2*3=6 3*3=9
1*4=4 2*4=8 3*4=12 4*4=16
1*5=5 2*5=10 3*5=15 4*5=20 5*5=25
1*6=6 2*6=12 3*6=18 4*6=24 5*6=30 6*6=36
1*7=7 2*7=14 3*7=21 4*7=28 5*7=35 6*7=42 7*7=49
1*8=8 2*8=16 3*8=24 4*8=32 5*8=40 6*8=48 7*8=56 8*8=64
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 selected

csdn上面转成汉字了，按照上面的思路加一些字符串处理，就可以实现
SQL>  with t as
2       (select rownum r from dual connect by rownum < 10)
3      select translate(substr(listagg(x,' ')within group (order by X),1, case
4                      when a = 1 then
5                       4
6                       when a*a<10 then  instr(listagg(x, ' ') within group(order by x), a||a) + 3
7                      else
8                       instr(listagg(x, ' ') within group(order by x), a||a) + 6
9                    end),'123456789','一二三四五六七八九') from (
10      select rpad(b||'得'||case when length(c)=1 then translate(c,'123456789','一二三四五六七八九'）
11      when length(c)=2 then translate(substr(c,1,1),'123456789','一二三四五六七八九'）||'十'||
12         translate(substr(c,2,1),'1234567890','一二三四五六七八九'） end,12,' ') x,A from (
13     select t2.r||t1.r b,T1.R*T2.R c ,t1.r a
14     from t t1,t t2)
15     ) group by A ORDER BY A
16     ;

TRANSLATE(SUBSTR(LISTAGG(X,'')
--------------------------------------------------------------------------------

• 博文量
20
• 访问量
29615