View 테이블¶

CREATE VIEW project_summary_view AS
select t1.*,
       IFNULL(t2.worker_cnt, 0) as worker_cnt, IFNULL(t2.insp_cnt, 0) as insp_cnt, IFNULL(t2.uploader_cnt, 0) as uploader_cnt
from (
    select p.project_id,
       p.project_nm,
       p.data_type,
       count(pd.data_id) as tot_cnt,
       iFNULL(SUM(case when data_status_ccd = 'ING' and data_dtl_status_ccd = 'ASSIGN' then 1 else 0 end) , 0) as assign_done_cnt,
       iFNULL(SUM(case when data_status_ccd = 'WAIT' and data_dtl_status_ccd = 'WAIT' then 1 else 0 end), 0)  as assign_wait_cnt ,
       iFNULL(SUM(case when data_status_ccd = 'ISSUE' then 1 else 0 end) , 0) as issue_cnt ,
       iFNULL(SUM(case when data_status_ccd = 'DONE' and data_dtl_status_ccd = 'DONE' then 1 else 0 end), 0)  as done_cnt,
       iFNULL(SUM(case when data_type='TABLE' and data_status_ccd = 'ING' and data_dtl_status_ccd = 'APPR' then 1 else 0 end), 0)  as appr_cnt
from project p
left 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 ;