sql 通过时间分组

最新推荐文章于 2024-08-08 10:07:10 发布

每在

最新推荐文章于 2024-08-08 10:07:10 发布

阅读量1.4k

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.youkuaiyun.com/weixin_42759564/article/details/120056952

版权

sql 通过时间分组

/*一周内每一天的日期*/
SELECT TO_CHAR((trunc(to_date('09/01','mm/dd'), 'iw')) + ROWNUM - 1, 'MM/dd') as time_date FROM DUAL
  CONNECT BY ROWNUM <= 7;

/*一月内每一天的日期*/
SELECT TO_CHAR((trunc(to_date('2021-09-01','yyyy-mm-dd'), 'mm')) + ROWNUM - 1, 'yyyy-MM-dd') as time_date FROM DUAL
CONNECT BY ROWNUM <= to_char(last_day(to_date('2021-09-01','yyyy-mm-dd')),'dd');
/*一年内每个月份*/
select to_char(add_months(sysdate,-(select to_char(trunc(sysdate),'MM')from dual )+level ),'MM') as time_date
from dual connect by level<=12;``

-- 通过本月每日
select m.time_date, case when d.elementt > 0 then d.elementt else 0 end elementt
from (SELECT TO_CHAR((trunc(to_date('09/01', 'mm/dd'), 'mm')) + ROWNUM - 1, 'MM/dd') as time_date
      FROM DUAL
      CONNECT BY ROWNUM <= to_char(last_day(to_date('09/01', 'mm/dd')), 'dd')) m
         left join (
    SELECT to_char(t.UPDATE_TIME, 'mm/dd') as UPDATE_TIME,
           count(t.id)                     as elementt
    from CE_EXTEND t
--     where t.UPDATE_TIME BETWEEN to_date('2021-08-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and TO_DATE('2021-09-05 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
    GROUP BY to_char(t.UPDATE_TIME, 'mm/dd')
) d on m.time_date = d.UPDATE_TIME order by m.time_date ASC;

-- 通过本周每日
select m.time_date, case when d.elementt > 0 then d.elementt else 0 end elementt
from (SELECT TO_CHAR((trunc(to_date('09/01','MM/dd'), 'iw')) + ROWNUM - 1, 'MM/dd') as time_date FROM DUAL
  CONNECT BY ROWNUM <= 7) m
         left join (
    SELECT to_char(t.UPDATE_TIME, 'mm/dd') as UPDATE_TIME,
           count(t.id)                     as elementt
    from CE_EXTEND t
--     where t.UPDATE_TIME BETWEEN to_date('2021-08-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and TO_DATE('2021-09-05 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
    GROUP BY to_char(t.UPDATE_TIME, 'mm/dd')
) d on m.time_date = d.UPDATE_TIME
order by m.time_date ASC ;


--本年每月
select m.time_date, case when d.elementt > 0 then d.elementt else 0 end elementt
from (select to_char(add_months(sysdate,-(select to_char(trunc(sysdate),'MM')from dual )+level ),'MM') as time_date
from dual connect by level<=12) m
         left join (
    SELECT to_char(t.UPDATE_TIME, 'MM') as UPDATE_TIME,
           count(t.id)                     as elementt
    from CE_EXTEND t
    GROUP BY to_char(t.UPDATE_TIME, 'MM')
) d on m.time_date = d.UPDATE_TIME
order by m.time_date ASC ;