用于合并记录的SQL查询

这是一个缺口和孤岛问题,但具有日期范围。最一般的解决方案是假设行之间可能存在间隙(尽管您的数据没有这样的间隙)。

你可以解决这个问题,找出“恒常期”从哪里开始。在这种情况下, lag() 是你的朋友。然后,当您找到它们的起始位置时,一个累积和标识组,聚合解决了问题:

select employee_id, employee_type,
       min(start_date), max(end_date)
from (select e.*,
             sum(case when start_date = dateadd(day, 1, prev_end_date) then 1 else 0 end) over
                 (partition by employee_id, employee_type) as grp
      from (select e.*,
                   lag(end_date) over (partition by employee_id, employee_type order by start_date) as prev_end_date
            from employee e
           ) e
     ) e
group by employee_id, employee_type, grp;

这应该有帮助,尽管您应该真正标记目标数据库,并避免随机标记。

用于SQL Server和MySQL

SELECT 
    employee_id
,   employee_type 
,   MIN(start_date) start_date
,   MAX(end_date) end_date
FROM 
    EMPLOYEE 
GROUP BY 
    employee_id
,   employee_type 
,   YEAR(end_date)
ORDER BY start_date

甲骨文

SELECT 
    employee_id
,   employee_type 
,   MIN(start_date) start_date
,   MAX(end_date) end_date
FROM 
    EMPLOYEE 
GROUP BY 
    employee_id
,   employee_type 
,   extract(year from end_date)
ORDER BY start_date

演示:

SQL Server

Oracle

MySQL

下面的查询帮助解决了问题

SELECT employee_id,
       employee_type,
       MIN(start_date) ,
       MAX(end_date) 
FROM   (SELECT *,
DENSE_RANK() OVER (PARTITION BY employee_id ORDER BY start_date),
DENSE_RANK() OVER (PARTITION BY employee_id, employee_type ORDER BY start_date),
                DENSE_RANK() OVER (PARTITION BY employee_id ORDER BY start_date) - 
                DENSE_RANK() OVER (PARTITION BY employee_id, employee_type ORDER BY start_date) AS Grp
         FROM   employee) T
GROUP  BY employee_id,
       employee_type,
          Grp
ORDER  BY 3 asc