按照每个标段分类查询 最近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\'