Блог

Я пытаюсь объединить историю заданий каждого сотрудника в схеме HR. История заданий каждого сотрудника должна быть агрегирована по ключу JobHistory. Я хочу только вернуть сотрудников, имеющих минимум одну запись в таблице job_history. С моим текущим запросом я также возвращаю сотрудников, не имеющих предыдущей истории заданий. Как мне исключить сотрудников без истории заданий из моего результирующего набора?

Спасибо.

 SELECT JSON_OBJECT(  
           KEY 'empId' VALUE E.EMPLOYEE_ID, 
           KEY 'firstName' VALUE E.FIRST_NAME,
           KEY 'lastName' VALUE E.LAST_NAME,
           KEY 'jobHistory' VALUE 
              ( 
              SELECT     JSON_OBJECTAGG
                                    ( 
                                     KEY 'job' VALUE json_object
                                        ( 
                                            KEY 'jobId' VALUE J.JOB_ID, 
                                            KEY 'startDate' VALUE J.START_DATE,
                                            KEY 'endDate' VALUE J.END_DATE,
                                            KEY 'departmentId' VALUE J.DEPARTMENT_ID

                                        ) 
                                    )
                                FROM   HR.JOB_HISTORY J
              WHERE J.EMPLOYEE_ID = E.EMPLOYEE_ID
                                ) 
              )
FROM   HR.EMPLOYEES E;
 

JOB_HISTORY Необходимо агрегировать перед объединением с сотрудниками. Итак, выполните a group by JSON_OBJECTAGG в подзапросе или a CTE , а затем присоединитесь к нему с помощью employees

 SELECT JSON_OBJECT(  
           KEY 'empId'       VALUE E.EMPLOYEE_ID, 
           KEY 'firstName'   VALUE E.FIRST_NAME,
           KEY 'lastName' .  VALUE E.LAST_NAME,
           KEY 'jobHistory'  VALUE s.prev_jobs
           ) as j
           FROM 
              ( 
              SELECT   J.employee_id,JSON_OBJECTAGG
                                    ( 
                                     KEY 'job' VALUE json_object
                                        ( 
                                            KEY 'jobId' VALUE J.JOB_ID, 
                                            KEY 'startDate' VALUE J.START_DATE,
                                            KEY 'endDate' VALUE J.END_DATE,
                                            KEY 'departmentId' VALUE J.DEPARTMENT_ID

                                        ) 
                                    ) as prev_jobs
                                FROM   HR.JOB_HISTORY J 
              GROUP BY J.employee_id
                                )  s
JOIN   HR.EMPLOYEES E on e.EMPLOYEE_ID = s.EMPLOYEE_ID
ORDER BY e.employee_id;
 

Результат

  J                                                                                                                                                                                                                                                                                                        
{"empId":101,"firstName":"Neena","lastName":"Kochhar","jobHistory":{"job":{"jobId":"AC_ACCOUNT","startDate":"1997-09-21T00:00:00","endDate":"2001-10-27T00:00:00","departmentId":110},"job":{"jobId":"AC_MGR","startDate":"2001-10-28T00:00:00","endDate":"2005-03-15T00:00:00","departmentId":110}}}     
{"empId":102,"firstName":"Lex","lastName":"De Haan","jobHistory":{"job":{"jobId":"IT_PROG","startDate":"2001-01-13T00:00:00","endDate":"2006-07-24T00:00:00","departmentId":60}}}                                                                                                                         
{"empId":114,"firstName":"Den","lastName":"Raphaely","jobHistory":{"job":{"jobId":"ST_CLERK","startDate":"2006-03-24T00:00:00","endDate":"2007-12-31T00:00:00","departmentId":50}}}                                                                                                                       
{"empId":122,"firstName":"Payam","lastName":"Kaufling","jobHistory":{"job":{"jobId":"ST_CLERK","startDate":"2007-01-01T00:00:00","endDate":"2007-12-31T00:00:00","departmentId":50}}}                                                                                                                     
{"empId":176,"firstName":"Jonathon","lastName":"Taylor","jobHistory":{"job":{"jobId":"SA_REP","startDate":"2006-03-24T00:00:00","endDate":"2006-12-31T00:00:00","departmentId":80},"job":{"jobId":"SA_MAN","startDate":"2007-01-01T00:00:00","endDate":"2007-12-31T00:00:00","departmentId":80}}}         
{"empId":200,"firstName":"Jennifer","lastName":"Whalen","jobHistory":{"job":{"jobId":"AD_ASST","startDate":"1995-09-17T00:00:00","endDate":"2001-06-17T00:00:00","departmentId":90},"job":{"jobId":"AC_ACCOUNT","startDate":"2002-07-01T00:00:00","endDate":"2006-12-31T00:00:00","departmentId":90}}}    
{"empId":201,"firstName":"Michael","lastName":"Hartstein","jobHistory":{"job":{"jobId":"MK_REP","startDate":"2004-02-17T00:00:00","endDate":"2007-12-19T00:00:00","departmentId":20}}}                                                                                                                    

7 rows selected.