Oracle 生成九九乘法表、日历

九九乘法表

SQL语句

select DECODE(v.a, NULL, NULL, '1 * ' || v.a || ' = ' || (1 * v.a)) A,
       DECODE(V.b, NULL, NULL, '2 * ' || v.b || ' = ' || (2 * v.b)) B,
       DECODE(v.c, NULL, NULL, '3 * ' || v.c || ' = ' || (3 * v.c)) C,
       DECODE(v.d, NULL, NULL, '4 * ' || v.d || ' = ' || (4 * v.d)) D,
       DECODE(v.e, NULL, NULL, '5 * ' || v.e || ' = ' || (5 * v.e)) E,
       DECODE(v.f, NULL, NULL, '6 * ' || v.f || ' = ' || (6 * v.f)) F,
       DECODE(v.g, NULL, NULL, '7 * ' || v.g || ' = ' || (7 * v.g)) G,
       DECODE(v.h, NULL, NULL, '8 * ' || v.h || ' = ' || (8 * v.h)) H,
       DECODE(v.i, NULL, NULL, '9 * ' || v.i || ' = ' || (9 * v.i)) I
  from (select level a,
               case
                 when level > 1 then
                  level
                 else
                  null
               end b,
               case
                 when level > 2 then
                  level
                 else
                  null
               end c,
               case
                 when level > 3 then
                  level
                 else
                  null
               end d,
               case
                 when level > 4 then
                  level
                 else
                  null
               end e,
               case
                 when level > 5 then
                  level
                 else
                  null
               end f,
               case
                 when level > 6 then
                  level
                 else
                  null
               end g,
               case
                 when level > 7 then
                  level
                 else
                  null
               end h,
               case
                 when level > 8 then
                  level
                 else
                  null
               end i
          from dual
        connect by level <= 9) v;

日历

SQL语句

with a as
 (select t.first_day,
         to_number(to_char(last_day(t.first_day), 'dd')) last_day
    from (select to_date(&year || '-' || case
                           when level < 10 then
                            '0' || level
                           else
                            to_char(level)
                         end || '-01',
                         'yyyy-mm-dd') first_day
            from dual
          connect by level <= 12) t),
b as
 (select a.first_day + (level - 1) myt
    from a
  connect by level <= a.last_day
         and prior a.first_day = a.first_day
         and prior dbms_random.value is not null),
c as
 (select to_char(b.myt, 'yyyy-mm') y,
         b.myt r,
         to_char(b.myt, 'd') xq,
         case
           when (to_char(to_date(to_char(b.myt, 'yyyy') || '0101', 'yyyymmdd'),
                         'd') > '1') and
                (to_char(b.myt, 'd') <
                to_char(to_date(to_char(b.myt, 'yyyy') || '0101', 'yyyymmdd'),
                         'd')) then
            to_char(b.myt, 'ww') + 1
           else
            to_number(to_char(b.myt, 'ww'))
         end z
    from b
   order by b.myt asc)
select c.y "年月",
       c.z "周",
       sum(decode(c.xq, 1, to_number(to_char(c.r, 'dd')))) "星期天",
       sum(decode(c.xq, 2, to_number(to_char(c.r, 'dd')))) "星期一",
       sum(decode(c.xq, 3, to_number(to_char(c.r, 'dd')))) "星期二",
       sum(decode(c.xq, 4, to_number(to_char(c.r, 'dd')))) "星期三",
       sum(decode(c.xq, 5, to_number(to_char(c.r, 'dd')))) "星期四",
       sum(decode(c.xq, 6, to_number(to_char(c.r, 'dd')))) "星期五",
       sum(decode(c.xq, 7, to_number(to_char(c.r, 'dd')))) "星期六"
  from c
 group by c.y, c.z
 order by c.y, c.z;

select y "月",
       nz "当年周",
       sum(decode(xq, 1, to_number(to_char(d, 'dd')))) "星期天",
       sum(decode(xq, 2, to_number(to_char(d, 'dd')))) "星期一",
       sum(decode(xq, 3, to_number(to_char(d, 'dd')))) "星期二",
       sum(decode(xq, 4, to_number(to_char(d, 'dd')))) "星期三",
       sum(decode(xq, 5, to_number(to_char(d, 'dd')))) "星期四",
       sum(decode(xq, 6, to_number(to_char(d, 'dd')))) "星期五",
       sum(decode(xq, 7, to_number(to_char(d, 'dd')))) "星期六"
  from (select to_char(d, 'yyyy-mm') y,
               to_number(to_char(d, 'd')) xq,
               case
                 when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and
                      (to_char(d, 'd') <
                      to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) then
                  to_char(d, 'ww') + 1
                 else
                  to_number(to_char(d, 'ww'))
               end nz,
               d
          from (select to_date(&year || '0101', 'yyyymmdd') + (level - 1) d
                  from dual
                connect by level <=
                           to_char(to_date(&year || '1231', 'yyyymmdd'),
                                   'ddd'))
         order by d)
 group by y, nz
 order by y, nz;

select b.iw,
       b.mm,
       sum(decode(b.d, 2, b.dd)) "星期一",
       sum(decode(b.d, 3, b.dd)) "星期二",
       sum(decode(b.d, 4, b.dd)) "星期三",
       sum(decode(b.d, 5, b.dd)) "星期四",
       sum(decode(b.d, 6, b.dd)) "星期五",
       sum(decode(b.d, 7, b.dd)) "星期六",
       sum(decode(b.d, 1, b.dd)) "星期天"
  from (select case
                 when to_char(a.day, 'mm') = 12 and to_char(a.day, 'iw') = 1 then
                  to_char(a.day, 'iw') + 52
                 else
                  to_number(to_char(a.day, 'iw'))
               end iw,
               to_char(a.day, 'd') d,
               to_char(a.day, 'dd') dd,
               to_char(a.day, 'mm') mm,
               a.day
          from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 day
                  from dual
                connect by level <=
                           to_char(to_date(&year || '1231', 'yyyymmdd'),
                                   'ddd')) a) b
 group by b.iw, b.mm
 order by b.iw, b.mm;