一条SQL实现九九乘法表

最新推荐文章于 2026-05-20 06:46:54 发布

转载最新推荐文章于 2026-05-20 06:46:54 发布 · 780 阅读

标签

#sql #c

收录于

本文通过一个具体的SQL查询实例，展示了如何使用自连接和层级函数来生成一系列数值及其乘法运算结果。该查询利用了Oracle数据库的特性，包括LAG窗口函数和DECODE函数，实现了对不同层级数据的灵活处理。

select r1 || '*' || r1 || '=' || r1 * r1 A,
   decode(r2, '', '', r2 || '*' || r1 || '=' || r2 * r1) b,
   decode(r3, '', '', r3 || '*' || r1 || '=' || r3 * r1) C,
   decode(r4, '', '', r4 || '*' || r1 || '=' || r4 * r1) D,
   decode(r5, '', '', r5 || '*' || r1 || '=' || r5 * r1) E,
   decode(r6, '', '', r6 || '*' || r1 || '=' || r6 * r1) F,
   decode(r7, '', '', r7 || '*' || r1 || '=' || r7 * r1) G,
   decode(r8, '', '', r8 || '*' || r1 || '=' || r8 * r1) H,
   decode(r9, '', '', r9 || '*' || r1 || '=' || r9 * r1) I
from (select level r1,
            lag(level, 1) over(order by level) r2,
            lag(level, 2) over(order by level) r3,
            lag(level, 3) over(order by level) r4,
            lag(level, 4) over(order by level) r5,
            lag(level, 5) over(order by level) r6,
            lag(level, 6) over(order by level) r7,
            lag(level, 7) over(order by level) r8,
            lag(level, 8) over(order by level) r9
      from dual
      connect by level < 10)