检索雇员的第一次重新雇用和终止日期以及第二次重新雇用和终止日期

Question

我想写一个SQL查询来获取第一次重新雇用，终止日期和第二次重新雇用，员工的终止日期，如果员工没有这些日期，那么它将在结果中显示空白(null)。表名为per_periods，开始列is包含聘用/再聘用日期，实际终止日期包含终止日期。在图中，第1个术语表示重新雇用日期，第2个术语表示终止日期

表数据如下图所示:[1]

查询必须适用于所有记录谁能给我指路?

如果您需要更多信息，请在评论框中评论。[1]: https://i.stack.imgur.com/YSqKL.png

Answer 1

在Oracle，从版本12开始，您可以使用MATCH_RECOGNIZE:

SELECT *
FROM   table_name
MATCH_RECOGNIZE (
PARTITION BY person_id
ORDER     BY date_start
MEASURES
first_rehire.date_start               AS rehire1_start,
first_rehire.actual_termination_date  AS rehire1_end,
second_rehire.date_start              AS rehire2_start,
second_rehire.actual_termination_date AS rehire2_end
PATTERN (^ first_hire first_rehire? second_rehire?)
DEFINE first_hire AS 1 = 1
)

或者，在Oracle和MySQL中，您可以使用:

SELECT person_id,
rehire1_start,
rehire1_end,
rehire2_start,
rehire2_end
FROM   (
SELECT person_id,
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY date_start) AS rn,
LEAD(date_start, 1)
OVER (PARTITION BY person_id ORDER BY date_start) AS rehire1_start,
LEAD(actual_termination_date, 1)
OVER (PARTITION BY person_id ORDER BY date_start) AS rehire1_end,
LEAD(date_start, 2)
OVER (PARTITION BY person_id ORDER BY date_start) AS rehire2_start,
LEAD(actual_termination_date, 2)
OVER (PARTITION BY person_id ORDER BY date_start) AS rehire2_end
FROM   table_name
) t
WHERE  rn = 1;

对于样本数据:

CREATE TABLE table_name (person_id, date_start, actual_termination_date) AS
SELECT 1, TIMESTAMP '1970-01-01 00:00:00 UTC', TIMESTAMP '1971-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '1972-01-01 00:00:00 UTC', TIMESTAMP '1973-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '1974-01-01 00:00:00 UTC', TIMESTAMP '1975-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '1976-01-01 00:00:00 UTC', TIMESTAMP '1977-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '1970-01-01 00:00:00 UTC', TIMESTAMP '1971-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '1972-01-01 00:00:00 UTC', TIMESTAMP '1973-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '1974-01-01 00:00:00 UTC', TIMESTAMP '1975-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 3, TIMESTAMP '1970-01-01 00:00:00 UTC', TIMESTAMP '1971-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 3, TIMESTAMP '1972-01-01 00:00:00 UTC', TIMESTAMP '1973-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 4, TIMESTAMP '1970-01-01 00:00:00 UTC', TIMESTAMP '1971-01-01 00:00:00 UTC' FROM DUAL;

两输出:

REHIRE2_END空空