时间戳

首页 > 解决方案 > Oracle SQL - 扫描属性更改

sql - Oracle SQL - 扫描属性更改

问题描述

我有以下员工表

 EMPID   RECORD_DATE   DEPARTMENT
 123456  2020-01-01    HR
 123456  2020-02-01    HR
 123456  2020-03-01    FINANCE
 123456  2020-04-01    FINANCE

 987654  2020-01-01    HR
 987654  2020-02-01    HR
 987654  2020-03-01    HR
 987654  2020-04-01    LEGAL

使用 Oracle PL/SQL，我需要构建一个表达式来确定员工流动列表，特别是那些从 HR 转移到任何其他（非 HR）部门的人员。

预期结果：

 EMPID   MOVEMENT_DATE DEPT_BEFORE DEPT_AFTER
 123456  2020-03-01    HR          FINANCE
 987654  2020-04-01    HR          LEGAL

我知道您可以使用 Lead 或 Lag 功能，但对我来说有点偏离：

 SELECT 
 ,EMP
 ,RECORD_DATE
 ,LAG(DEPARTMENT, 1, 0) OVER (PARTITION BY EMP ORDER BY RECORD_DATE)  PREV

 FROM EMP

以下是一些可以使用的值：

 CREATE TABLE #EMP 
 (
     EMP VARCHAR(30) NOT NULL  ,
     RECORD_DATE DATE NOT NULL ,
     DEPARTMENT VARCHAR(30) NOT NULL
 );

 INSERT INTO #EMP (EMP, DATE_WORKED, CITY) 
 VALUES 
 ('123456','2020-01-01','HR'),
 ('123456','2020-02-01','HR'),
 ('123456','2020-03-01','FINANCE'),
 ('123456','2020-04-01','FINANCE'),

 ('987654','2020-01-01','HR'),
 ('987654','2020-02-01','HR'),
 ('987654','2020-03-01','HR'),
 ('987654','2020-04-01','LEGAL')

标签： sqloracle

解决方案

你可以使用LAG函数来做到这一点：

WITH data AS(
  SELECT 123456 EMPID, DATE '2020-01-01' RECORD_DATE, 'HR' DEPARTMENT FROM dual UNION ALL
   SELECT 123456 EMPID, DATE '2020-02-01' RECORD_DATE, 'HR' DEPARTMENT FROM dual UNION ALL
   SELECT 123456 EMPID, DATE '2020-03-01' RECORD_DATE, 'FINANCE' DEPARTMENT FROM dual UNION ALL
   SELECT 123456 EMPID, DATE '2020-04-01' RECORD_DATE, 'FINANCE' DEPARTMENT FROM dual UNION ALL
   SELECT 987654 EMPID, DATE '2020-01-01' RECORD_DATE, 'HR' DEPARTMENT FROM dual UNION ALL
   SELECT 987654 EMPID, DATE '2020-02-01' RECORD_DATE, 'HR' DEPARTMENT FROM dual UNION ALL
   SELECT 987654 EMPID, DATE '2020-03-01' RECORD_DATE, 'HR' DEPARTMENT FROM dual UNION ALL
   SELECT 987654 EMPID, DATE '2020-04-01' RECORD_DATE, 'LEGAL' DEPARTMENT FROM dual
 )
SELECT * FROM(
    SELECT 
      EMPID,
      RECORD_DATE MOVEMENT_DATE,
      LAG(DEPARTMENT) OVER (PARTITION BY EMPID ORDER BY RECORD_DATE)  DEPARTMENT_BEFORE,
      DEPARTMENT DEPARTMENT_AFTER
     FROM data
 )
 WHERE DEPARTMENT_BEFORE <> DEPARTMENT_AFTER;

     EMPID MOVEMENT_DATE   DEPARTMENT_BEFORE DEPARTMENT_AFTER 
---------- --------------- ----------------- -----------------
    123456 2020-03-01      HR                FINANCE          
    987654 2020-04-01      HR                LEGAL

推荐阅读