• oracle 查询每月数据,不足补0



    select t2.datevalue extractDate,nvl(t1.p_Consult_Num, 0) p_Consult_Num
    from (

    select TO_CHAR(EXTRACT_DATE, 'yyyy-mm') extractDate,sum(Consult_Num) p_Consult_Num
    from Service_STATS_Tbl
    where EXTRACT_DATE>=TO_DATE('2018-01-12', 'yyyy-mm-dd') and EXTRACT_DATE<= TO_DATE('2018-11-12', 'yyyy-mm-dd')
    group by TO_CHAR(EXTRACT_DATE, 'yyyy-mm')

    ) t1,
    (
    SELECT TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR('2019-01-12', 1, 7), 'YYYY-MM'), ROWNUM - 1),'YYYY-MM') datevalue
    FROM DUAL
    CONNECT BY ROWNUM <=
    MONTHS_BETWEEN(TO_DATE(SUBSTR('2019-11-12', 1, 7), 'yyyy-mm'),TO_DATE(SUBSTR('2019-01-12', 1, 7), 'yyyy-mm')) + 1
    ) t2
    where t1.extractDate(+) = t2.datevalue
    order by t2.datevalue

  • 相关阅读:
    sed&awk 资料汇总 全是链接
    LeetCode Path 3Sum
    C++ mem_fun
    递归绑定
    查询当天数据
    清除script注入
    防注入查询
    我的最新分页
    群发邮件
    利用缓存
  • 原文地址:https://www.cnblogs.com/tong2018/p/13792348.html
Copyright © 2020-2023  润新知