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;