View 테이블¶

CREATE VIEW project_summary_view AS
select t1.*, t2.worker_cnt, t2.insp_cnt, t2.uploader_cnt from (
    select p.project_id,
       p.project_nm,
       p.data_type,
       count(*) as tot_cnt,
       SUM(case when data_status_ccd = 'ING' and data_dtl_status_ccd = 'ASSIGN' then 1 else 0 end)  as assign_done_cnt,
       SUM(case when data_status_ccd = 'WAIT' and data_dtl_status_ccd = 'WAIT' then 1 else 0 end)  as assign_wait_cnt ,
       SUM(case when data_status_ccd = 'ISSUE' then 1 else 0 end)  as issue_cnt ,
       SUM(case when data_status_ccd = 'DONE' and data_dtl_status_ccd = 'DONE' then 1 else 0 end)  as done_cnt
from project p
inner join project_data pd on p.project_id = pd.project_id
group by p.project_id
order by p.project_id
              ) t1
left join (
    select project_id,
           sum(case when user_role = 'WORKER' then 1 else 0 end) as worker_cnt,
           sum(case when user_role = 'INSPECTOR' then 1 else 0 end) as insp_cnt,
           sum(case when user_role = 'UPLOADER' then 1 else 0 end) as uploader_cnt
    from project_user_assigned pua
    join users u on u.user_seq = pua.user_seq
    group by project_id
    order by project_id
) t2 on t1.project_id = t2.project_id ;