开发过程中Orcale学习记录

需求：
1. 只查询树的第三层之外的节点数据（包括第三层数据）
2. 查询上面条件过滤后数据的节点总数，每条数据有个字段status，当status为25时为已完成，反之为未完成
   已完成数量:节点总数/已完成数量
   未完成数量:节点总数/未完成数量

select *
  from pmm_project_plan ppp
  left join pmm_project pp
    on ppp.pmm_project_id = pp.id
 where pp.project_numberd_d like '%' || 'wangxiang2' || '%'
 
   and not exists
 (select t.id
          from (select t.id, level as leves
                  from PMM_PROJECT_PLAN t
                connect by prior t.id = t.parent_project_plan_id
                 start with t.parent_project_plan_id = '-1') t
         where leves <= 2
           and t.id = ppp.id)

select nums,
      finished || '/' || decode(nums,0,0,round(finished / nums * 100, 2)) || '%' as finished,
      unfinished || '/' || decode(nums,0,0,round(unfinished / nums * 100, 2)) || '%' as unfinished
  from (select count(*) as nums,
               nvl(sum(case status
                     when '25' then
                      1
                     else
                      0
                   end),0) as finished,
               nvl(sum(case status
                     when '25' then
                      0
                     else
                      1
                   end),0) as unfinished
          from (select *
                  from pmm_project_plan ppp
                  left join pmm_project pp
                    on ppp.pmm_project_id = pp.id
                 where pp.project_numberd_d  like '%'||'wangxiang2'||'%'
                 
                   and not exists
                 (select t.id
                          from (select t.id, level as leves
                                  from PMM_PROJECT_PLAN t
                                connect by prior t.id = t.parent_project_plan_id
                                 start with t.parent_project_plan_id = '-1') t
                         where leves <= 2
                           and t.id = ppp.id)))

nvl(sum(case status
    when '25' then
       1
    else
       0
    end),0)

round(finished / nums * 100, 2)

decode(nums,0,0,round(finished / nums * 100, 2))

with mcs_data as
 (select t1.str_year,
          t1.str_quarter,
          t1.oversize_quality
    from MCS_PRODUCT_OVERSIZE_COUNT t1
    where t1.str_year = '2015') --此查询查到上图表格一数据

select *
  from mcs_data pivot(sum(oversize_quality) as oversize for str_quarter in(1 as one,
                                                                           2 as two,
                                                                           3 as three,
                                                                           4 as four))
 order by str_year

with mcs_data as
 (select t1.str_year,
          t1.str_quarter,
          t1.oversize_quality,
          t1.scrapped_quality
    from MCS_PRODUCT_OVERSIZE_COUNT t1
    where t1.str_year = '2015')

select *
  from mcs_data pivot(sum(oversize_quality) as oversize,sum(scrapped_quality) as scrapped for str_quarter in(1 as one,
                                                                                                             2 as two,
                                                                                                             3 as three,
                                                                                                             4 as four))
 order by str_year

with data_list as
 (select t.str_year,
         t.str_quarter,
         t.mds_item_id,
         mi.item_name mds_item_name,
         (select sum(p.target_value) target_value
            from MCS_PRODUCT_OVERSIZE_COUNT p
           where p.str_year = t.str_year
             and p.mds_item_id = t.mds_item_id
           group by p.str_year, p.mds_item_id) target_value,
         sum(t.oversize_quality) as oversize_quality,
         sum(t.scrapped_quality) as scrapped_quality,
         sum(t.count_qty) as count_qty
    from MCS_PRODUCT_OVERSIZE_COUNT t
    left join mds_item mi
      on t.mds_item_id = mi.id
   group by t.str_year, t.str_quarter, t.mds_item_id, mi.item_name)

select *
  from data_list pivot(sum(oversize_quality) as oversize, sum(scrapped_quality) as scrapped, sum(count_qty) as count_qty for str_quarter in(1 as one,
                                                                                                                                            2 as two,
                                                                                                                                            3 as three,
                                                                                                                                            4 as four))
--where条件
 order by str_year

SELECT s.parent_register_id,
       LISTAGG(s.attach_size || ',' || s.attach_name, ';') within group(order by s.creation_date) AS ATTACH_MESAGE
  FROM Sys_Attachment s
 WHERE S.parent_table_id = 'GK_PROBLEM_MANAGE_FEEDBACK'
 GROUP BY s.parent_register_id;

SELECT s.parent_register_id,
    LISTAGG(s.attach_size || ',' || s.attach_name, ';') within group(order by s.creation_date) over(partition by s.parent_register_id) AS ATTACH_MESAGE
FROM Sys_Attachment s
WHERE S.parent_table_id = 'GK_PROBLEM_MANAGE_FEEDBACK'