小九九
来源:互联网 发布:淘宝搜索引擎规则 编辑:程序博客网 时间:2024/03/29 15:09
with l as (select level as lv from dual connect by level <= 9),m as (select a.lv as lv_a, b.lv as lv_b, to_char(b.lv) || ' × ' || to_char(a.lv) || ' = ' || rpad(to_char(a.lv * b.lv), 2, ' ') as text from l a, l b where b.lv <= a.lv)select listagg(m.text, ' ') within group(order by m.lv_b) from m group by m.lv_a;1 × 1 = 11 × 2 = 2 2 × 2 = 41 × 3 = 3 2 × 3 = 6 3 × 3 = 91 × 4 = 4 2 × 4 = 8 3 × 4 = 12 4 × 4 = 161 × 5 = 5 2 × 5 = 10 3 × 5 = 15 4 × 5 = 20 5 × 5 = 251 × 6 = 6 2 × 6 = 12 3 × 6 = 18 4 × 6 = 24 5 × 6 = 30 6 × 6 = 361 × 7 = 7 2 × 7 = 14 3 × 7 = 21 4 × 7 = 28 5 × 7 = 35 6 × 7 = 42 7 × 7 = 491 × 8 = 8 2 × 8 = 16 3 × 8 = 24 4 × 8 = 32 5 × 8 = 40 6 × 8 = 48 7 × 8 = 56 8 × 8 = 641 × 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
with l as (select level as lv from dual connect by level <= 9),m as (select a.lv as lv_a, b.lv as lv_b, to_char(b.lv) || ' × ' || to_char(a.lv) || ' = ' || rpad(to_char(a.lv * b.lv), 2, ' ') as text from l a, l b where b.lv <= a.lv)select "1","2","3","4","5","6","7","8","9" from mpivot(max(m.text) for lv_b in(1,2,3,4,5,6,7,8,9))order by 1;1 2 3 4 5 6 7 8 9------------- -------------- -------------- -------------- ------------- ------------- ------------- ------------- -----------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 = 819 rows selected