基于postgresql中特定条件的俱乐部两张表

这个问题属于缺口和岛屿分类学.这个特定的变种可以分三步解决:

• 应用这两个表中的UNION ALL个，同时在同一模式内的两个单独的字段中拆分"tab1.project"和"tab2.role"
• 使用两个运行和(一个用于"designation"，另一个用于"project")计算非空值和后续空值之间的分区.
• 对两个不同的字段应用两个不同的聚合，以删除空值.

WITH cte AS (
    SELECT employee_id, effective_date,
           project        AS project, 
           NULL           AS role         FROM tab1
    UNION ALL 
    SELECT employee_id, effective_date, 
           NULL           AS project, 
           designation    AS role         FROM tab2
), cte2 AS (
    SELECT *,
           COUNT(CASE WHEN project IS NOT NULL THEN 1 END) OVER(
               PARTITION BY employee_id 
               ORDER     BY effective_date
           ) AS project_partition,
           COUNT(CASE WHEN role IS NOT NULL THEN 1 END) OVER(
               PARTITION BY employee_id 
               ORDER     BY effective_date
           ) AS role_partition
    FROM cte    
)
SELECT employee_id, effective_date,
       MAX(project) OVER(PARTITION BY project_partition) AS project,
       MAX(role)    OVER(PARTITION BY role_partition)    AS role
FROM cte2
ORDER BY employee_id, effective_date

查看演示here.