oracle sql 周日查询,oracle sql查询日历

查询当前时间所在月份的日历

select sum(d1) 星期日,

sum(d2) 星期一,

sum(d3) 星期二,

sum(d4) 星期三,

sum(d5) 星期四,

sum(d6) 星期五,

sum(d7) 星期六

from (select decode(d, 1, l) d1,

decode(d, 2, l) d2,

decode(d, 3, l) d3,

decode(d, 4, l) d4,

decode(d, 5, l) d5,

decode(d, 6, l) d6,

decode(d, 7, l) d7,

case

when d >= fd then

row_number() over(partition by d order by l) - 1

else

row_number() over(partition by d order by l)

end ro

from (select level l,--日期

to_char(add_months(last_day(sysdate), -1) + level, 'd') d,--星期几

to_char(add_months(last_day(sysdate), -1) + 1, 'd') fd --第一天星期几

from dual

connect by level <= to_char(last_day(sysdate), 'dd')))

group by ro

order by nvl(sum(d1) ,0)