SQL实现九九乘法口诀表

oracle pl/sql:

--输出屏幕信息: set serveroutput on;
--打印口诀表
declare
  n1 number(3);  --外层循环变量
  n2 number(3);  --内层循环变量
begin
  for n1 in 1 .. 9   --开始外层循环
    loop
      --开始内层循环
    for n2 in 1 .. n1 
      loop
        --打印口诀内容
      dbms_output.put(n2||'*'||n1||'='||n1*n2||'  ');
    end loop;
    dbms_output.put_line('');  --输出换行
  end loop;
end;
/

oracle sql:

-- 九九乘法表
select  '1*' ||to_char(flag)||'='||to_char(flag)"1",
case when flag>1 then '2*'||to_char(flag)||'='||to_char(flag*2) else '' end "2",
case when flag>2 then '3*'||to_char(flag)||'='||to_char(flag*3) else '' end "3",
case when flag>3 then '4*'||to_char(flag)||'='||to_char(flag*4) else '' end "4",
case when flag>4 then '5*'||to_char(flag)||'='||to_char(flag*5) else '' end "5",
case when flag>5 then '6*'||to_char(flag)||'='||to_char(flag*6)else '' end "6",
case when flag>6 then '7*'||to_char(flag)||'='||to_char(flag*7) else '' end "7",
case when flag>7 then '8*'||to_char(flag)||'='||to_char(flag*8) else '' end "8",
case when flag>8 then '9*'||to_char(flag)||'='||to_char(flag*9) else '' end "9"
from (
select 1 flag from dual
union all
select 2 from dual
union all
select 3 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 6 from dual
union all
select 7 from dual
union all
select 8 from dual
union all
select 9 from dual) a;

第二种写法：

select max(decode(n2, 1, nn)) nn1,
       max(decode(n2, 2, nn)) nn2,
       max(decode(n2, 3, nn)) nn3,
       max(decode(n2, 4, nn)) nn4,
       max(decode(n2, 5, nn)) nn5,
       max(decode(n2, 6, nn)) nn6,
       max(decode(n2, 7, nn)) nn7,
       max(decode(n2, 8, nn)) nn8,
       max(decode(n2, 9, nn)) nn9
  from (select t1.n n1,
               t2.n n2,
               t1.n * t2.n,
               t1.n || '*' || t2.n || '=' || t1.n * t2.n nn
          from (select level n from dual connect by level <= 9) t1,
               (select level n from dual connect by level <= 9) t2
         where t1.n <= t2.n)
 group by n1
 order by n1 desc;

第二种写法plus：

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);

雕虫小技，聊慰君心，落之~