PostgreSQL查询实现：表1日期不匹配时取表2同期最大可用日期

JOIN vs WHERE 条件放置原则 #

• 若要控制关联匹配逻辑（比如只关联符合指定历史日期的记录），把条件写在JOIN ON子句中——这能提前过滤无效关联，避免不必要的笛卡尔积，性能更优。
• 若要过滤最终关联后的结果集，把条件写在WHERE子句中。

替代笛卡尔积的最优方案：窗口函数+CTE #

针对你需要获取MAX()可用History Date的需求，用窗口函数替代笛卡尔积是最高效的方案，逻辑清晰且性能碾压笛卡尔积（尤其数据量大时）。具体实现如下：

-- 封装表1、表2的子查询为CTE，提升可读性
WITH CTE_Table1 AS (
    -- 替换成你的表1专有字段子查询
    SELECT ID, 专有字段1, HistoryDate FROM 你的表1子查询语句
),
CTE_Table2 AS (
    -- 替换成你的表2专有字段子查询
    SELECT ID, 专有字段2, HistoryDate FROM 你的表2子查询语句
),
-- 给每个ID计算最大历史日期（窗口函数O(n)复杂度，远快于笛卡尔积O(n²)）
CTE_Table1_Max AS (
    SELECT *,
           MAX(HistoryDate) OVER (PARTITION BY ID) AS Max_History_Date
    FROM CTE_Table1
),
CTE_Table2_Max AS (
    SELECT *,
           MAX(HistoryDate) OVER (PARTITION BY ID) AS Max_History_Date
    FROM CTE_Table2
)
-- 关联两个带最大日期的数据集，按需筛选字段
SELECT 
    t1.ID,
    t1.专有字段1,
    t2.专有字段2,
    t1.Max_History_Date AS 可用历史日期
FROM CTE_Table1_Max t1
INNER JOIN CTE_Table2_Max t2
    ON t1.ID = t2.ID
    AND t1.Max_History_Date = t2.Max_History_Date -- 仅匹配双方最大历史日期的记录
-- 如需额外过滤，添加WHERE条件
WHERE t1.专有字段1 IS NOT NULL;

灵活调整场景 #

1. 仅基于某一方的最大日期关联：
   如果不需要严格匹配两边的最大日期，只需要用表1的最大日期关联表2，简化CTE：

   WITH CTE_Table1 AS (
       SELECT ID, 专有字段1, HistoryDate FROM 你的表1子查询语句
   ),
   CTE_Table2 AS (
       SELECT ID, 专有字段2, HistoryDate FROM 你的表2子查询语句
   ),
   CTE_Table1_Max AS (
       SELECT *,
              MAX(HistoryDate) OVER (PARTITION BY ID) AS Max_History_Date
       FROM CTE_Table1
   )
   SELECT 
       t1.ID, t1.专有字段1, t2.专有字段2, t1.Max_History_Date
   FROM CTE_Table1_Max t1
   JOIN CTE_Table2 t2
       ON t1.ID = t2.ID
       AND t2.HistoryDate = t1.Max_History_Date;
   

2. CASE语句的使用场景：
   如果需要根据历史日期输出自定义状态，比如标记过期记录：

   -- 复用前面的CTE
   SELECT 
       t1.ID,
       t1.专有字段1,
       t2.专有字段2,
       t1.Max_History_Date,
       CASE
           WHEN t1.Max_History_Date < '2023-01-01' THEN '过期'
           ELSE '有效'
       END AS 记录状态
   FROM CTE_Table1_Max t1
   JOIN CTE_Table2_Max t2
       ON t1.ID = t2.ID
       AND t1.Max_History_Date = t2.Max_History_Date;
   

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