如何去除SQL数据表两行的NULL值，合并得到匹配的起止日期对

你当前输出中的有效非空起始日期、结束日期数量完全匹配，仅需将两类非空值按时间顺序排序后一一配对，过滤空值即可得到目标结果。

调整后的完整SQL代码如下：

WITH intermediate_result AS (
    SELECT 
        (CASE WHEN (end_lag <> start_date) OR end_lag IS NULL THEN start_date END) AS start_date, 
        (CASE WHEN (start_lead <> end_date) OR start_lead IS NULL THEN end_date END) AS end_date
    FROM (
        SELECT 
            LEAD(start_date) OVER(ORDER BY start_date) AS start_lead, 
            start_date, 
            end_date, 
            LAG(end_date) OVER(ORDER BY end_date) AS end_lag
        FROM projects
    ) t1
)
SELECT a.start_date, b.end_date
FROM 
    (SELECT start_date, ROW_NUMBER() OVER (ORDER BY start_date) AS row_num 
     FROM intermediate_result 
     WHERE start_date IS NOT NULL) a
INNER JOIN 
    (SELECT end_date, ROW_NUMBER() OVER (ORDER BY end_date) AS row_num 
     FROM intermediate_result 
     WHERE end_date IS NOT NULL) b
ON a.row_num = b.row_num;

逻辑说明 #

• 复用你原有逻辑生成中间结果表，保留已计算出的起始、结束日期字段
• 分别提取中间结果里非空的起始日期、结束日期，按时间升序排序后生成同序列的行号
• 通过行号关联两个子查询，即可实现有序的起止日期配对，完全匹配你期望的输出结果

内容的提问来源于stack exchange，提问作者Rajee