Oracle SQL-如何选择和组合多行数据

Question

场景是，对于特定的日期范围，我需要返回字段值已更改（从和到日期）的所有行。更改后的字段值将以不同的颜色显示在屏幕上。

我可以返回特定时间段内发生更改的所有员工记录。但是，如果一个员工的记录在所选时间段内多次更改，我需要返回一个员工记录，并为每个不同的X_标志列返回一个组合值。1表示已发生更改，0表示未发生更改。

表DDL为：

CREATE TABLE "EMPLOYEE_DATA" 
   (    "EMPLOYEE_ID" NUMBER(20,0), 
    "EMPLOYEE_NAME" VARCHAR2(100 BYTE), 
    "EMPLOYEE_NAME_FLAG" NUMBER(1,0), 
    "EMPLOYEE_ROLE" VARCHAR2(100 BYTE), 
    "EMPLOYEE_ROLE_FLAG" NUMBER(1,0), 
    "EMPLOYEE_SALARY" VARCHAR2(100 BYTE), 
    "EMPLOYEE_SALARY_FLAG" NUMBER(1,0),  
    "DATE_VALID_FROM" DATE, 
    "DATE_VALID_TO" DATE, 
    "HAS_RECORD_CHANGED" NUMBER(1,0), 
    "CURRENT_ROW_IND" NUMBER(1,0)
   );

模拟数据是：

Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (1,'John Smith',0,'Associate',0,'1',0,to_date('01-FEB-17','DD-MON-RR'),to_date('28-FEB-17','DD-MON-RR'),0,0);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (2,'Katy Brown',0,'Team Leader',0,'7',0, to_date('01-FEB-17','DD-MON-RR'),to_date('28-FEB-17','DD-MON-RR'),0,0);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (2,'Katy Brown',0,'Team Leader',0,'7',0, to_date('01-APR-17','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),1,1);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (3,'Ian Jones',1,'Delivery Manager',1,'3',1, to_date('01-MAR-17','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),1,1);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (1,'John Smith',0,'Analyst',1,'1',0, to_date('01-MAR-17','DD-MON-RR'),to_date('31-MAR-17','DD-MON-RR'),1,0);
Insert into EMPLOYEE_DATA (EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_NAME_FLAG,EMPLOYEE_ROLE,EMPLOYEE_ROLE_FLAG,EMPLOYEE_SALARY,EMPLOYEE_SALARY_FLAG, DATE_VALID_FROM,DATE_VALID_TO,HAS_RECORD_CHANGED,CURRENT_ROW_IND) values (1,'John Smith',0,'Analyst',0,'2',1, to_date('01-APR-17','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),1,1);

我的疑问是：

SELECT *
FROM EMPLOYEE_DATA
WHERE DATE_VALID_FROM <= TO_DATE('01/04/2017', 'dd/mm/yyyy') 
  AND DATE_VALID_TO >= TO_DATE('01/01/2017', 'dd/mm/yyyy')
  AND HAS_RECORD_CHANGED = '1'
ORDER BY EMPLOYEE_ID ASC, DATE_VALID_FROM ASC;

在最后一个查询中，我将添加以下行以返回当前记录和当前行\u IND='1'。我省略了这一点，以说明我需要如何组合“John Smith”记录的数据，以合并上一条记录和当前记录（John Smith）的员工\角色\标志和员工\工资\标志

编辑-添加原始和目标结果。如果可能的话，我需要聚合以获得每个唯一员工的X_标志列的最大值。

原件

EMPLOYEE_ID    EMPLOYEE_NAME    EMPLOYEE_NAME_FLAG  EMPLOYEE_ROLE   EMPLOYEE_ROLE_FLAG  EMPLOYEE_SALARY EMPLOYEE_SALARY_FLAG    DATE_VALID_FROM DATE_VALID_TO   HAS_RECORD_CHANGED  CURRENT_ROW_IND
1              John Smith       0                   Associate               0                   1               0                   01-Feb-17         28-Feb-17     0                   0
2              Katy Brown       0                   Team Leader             0                   7               0                   01-Feb-17         28-Feb-17     0                   0
2              Katy Brown       0                   Team Leader             0                   7               0                   01-Apr-17         31-Dec-99     1                   1
3              Ian Jones        1                   Delivery Manager        1                   3               1                   01-Mar-17         31-Dec-99     1                   1
1              John Smith       0                   Analyst                 1                   0               0                   01-Mar-17         31-Mar-17     1                   0
1              John Smith       0                   Analyst                 0                   1               1                   01-Apr-17         31-Dec-99     1                   1

目标

EMPLOYEE_ID EMPLOYEE_NAME   EMPLOYEE_NAME_FLAG  EMPLOYEE_ROLE   EMPLOYEE_ROLE_FLAG  EMPLOYEE_SALARY EMPLOYEE_SALARY_FLAG    DATE_VALID_FROM DATE_VALID_TO   HAS_RECORD_CHANGED  CURRENT_ROW_IND
1           John Smith      0                   Analyst                     1                   1               1               01-Apr-17    31-Dec-99      1                   1
2           Katy Brown      0                   Team Leader                 0                   7               0               01-Apr-17    31-Dec-99      1                   1
3           Ian Jones       1                   Delivery Manager            1                   3               1               01-Mar-17    31-Dec-99      1                   1

Answer 1

如果可能的话，我需要聚合以获得每个唯一员工的X_标志列的最大值。

对于上述要求，groupby将起作用，对吗？

请查找以下查询以实现您的目标，

SELECT EMPLOYEE_ID
  , EMPLOYEE_NAME
  , MIN(EMPLOYEE_NAME_FLAG) EMPLOYEE_NAME_FLAG
  , EMPLOYEE_ROLE
  , MAX(EMPLOYEE_ROLE_FLAG) EMPLOYEE_ROLE_FLAG
  , MIN(EMPLOYEE_SALARY) EMPLOYEE_SALARY
  , MAX(EMPLOYEE_SALARY_FLAG) EMPLOYEE_SALARY_FLAG
  , MAX(DATE_VALID_FROM) DATE_VALID_FROM
  , MAX(DATE_VALID_TO) DATE_VALID_TO
  , HAS_RECORD_CHANGED
  , MAX(CURRENT_ROW_IND) CURRENT_ROW_IND
FROM EMPLOYEE_DATA
WHERE HAS_RECORD_CHANGED = 1
   AND DATE_VALID_FROM BETWEEN TO_DATE('01/01/2017', 'dd/mm/yyyy')
                             AND TO_DATE('01/04/2017', 'dd/mm/yyyy')
GROUP BY EMPLOYEE_ID
  , EMPLOYEE_NAME
  , EMPLOYEE_ROLE
  , HAS_RECORD_CHANGED
ORDER BY EMPLOYEE_ID ASC
  , DATE_VALID_FROM ASC;

Answer 2

考虑派生表，其中单元级查询与计算最大标志的聚合查询连接：

SELECT emp.*    
FROM
  (SELECT *
   FROM EMPLOYEE_DATA
   WHERE DATE_VALID_FROM BETWEEN TO_DATE('01/01/2017', 'dd/mm/yyyy')
                             AND TO_DATE('01/04/2017', 'dd/mm/yyyy')
     AND HAS_RECORD_CHANGED = 1
  ) AS emp

INNER JOIN
  (SELECT EMPLOYEE_ID, MAX(EMPLOYEE_NAME_FLAG) AS MAX_NAME_FLAG,
          MAX(EMPLOYEE_ROLE_FLAG) AS MAX_ROLE_FLAG, 
          MAX(EMPLOYEE_SALARY_FLAG) AS MAX_SALARY_FLAG
   FROM EMPLOYEE_DATA
   WHERE DATE_VALID_FROM BETWEEN TO_DATE('01/01/2017', 'dd/mm/yyyy')
                             AND TO_DATE('01/04/2017', 'dd/mm/yyyy')
     AND HAS_RECORD_CHANGED = 1
   GROUP BY EMPLOYEE_ID
  ) AS agg

ON emp.EMPLOYEE_ID = agg.EMPLOYEE_ID 
AND emp.EMPLOYEE_NAME_FLAG = agg.MAX_NAME_FLAG 
AND emp.EMPLOYEE_ROLE_FLAG = agg.MAX_ROLE_FLAG 
AND emp.EMPLOYEE_SALARY_FLAG = agg.MAX_SALARY_FLAG

ORDER BY emp.EMPLOYEE_ID ASC, emp.DATE_VALID_FROM ASC