按照每个分类查询 最近7天 的 4个状态的数据

--按照每个分类查询 最近7天 的 4个状态的数据
select 
    m.section_id as "sectionId",
      m.section_name as "sectionName",
        nvl(s.count1,0) as "countWtj",
        nvl(s.count2,0) as "countDzg",
        nvl(s.count3,0) as "countDfc",
        nvl(s.count4,0) as "countYzg",
      s.today as "day"
    FROM MATERIAL_SECTION_INFO m left join 
    ( 
    select * from
    (SELECT 
        to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') today
      FROM
        DUAL connect BY LEVEL <= 7) d
    LEFT JOIN 
        (select to_char(t.CREATE_TIME_,'YYYY-MM-dd') as day,
            t.SECT_ID_,
            SUM(CASE t.STATUS_ WHEN '1' THEN 1 ELSE 0 END) AS count1,
            SUM(CASE t.STATUS_ WHEN '2' THEN 1 ELSE 0 END) AS count2,
            SUM(CASE t.STATUS_ WHEN '3' THEN 1 ELSE 0 END) AS count3,
            SUM(CASE t.STATUS_ WHEN '4' THEN 1 ELSE 0 END) AS count4
        from QUALITY_PROBLEM t GROUP BY to_char(t.CREATE_TIME_,'YYYY-MM-dd'),t.SECT_ID_ 
        )b on d.today=b.day order by d.today
    ) s on m.section_id=s.SECT_ID_ and prjid='119993794937487360'