Project

일반

사용자정보

이력

Wiki »

View 테이블 » 이력 » 개정판 2

개정판 1 (김지영, 2023-07-12 08:24) → 개정판 2/3 (김지영, 2023-07-19 07:47) 

h1. View 테이블 

 <pre> 
 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 
 t2.worker_cnt, t2.insp_cnt, t2.uploader_cnt from ( 
     select p.project_id, 
        p.project_nm, 
        p.data_type, 
        count(*) as tot_cnt, 
        iFNULL(SUM(case 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 SUM(case when data_status_ccd = 'WAIT' and data_dtl_status_ccd = 'WAIT' then 1 else 0 end), 0) end)    as assign_wait_cnt , 
        iFNULL(SUM(case SUM(case when data_status_ccd = 'ISSUE' then 1 else 0 end) , 0)    as issue_cnt , 
        iFNULL(SUM(case SUM(case when data_status_ccd = 'DONE' and data_dtl_status_ccd = 'DONE' then 1 else 0 end), 0) end)    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 done_cnt 
 from project p 
 left 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 ; 
 

 </pre>