如何关联给定的两个SELECT查询语句（P01与P02）？

首先需要修正你原SQL中的语法错误：单个SELECT语句后不能直接用as P01定义别名，你需要用公用表表达式(CTE)或者子查询先定义这两个数据集，再进行关联操作。以下是两种常见的关联场景及实现代码：

场景1：合并两个结果集（UNION ALL） #

如果你的需求是把P01和P02的查询结果合并成一个完整数据集，需要保证两个查询的字段数量、类型一致，给P01补充Late字段（比如设为0）：

WITH P01 AS (
    SELECT Fld1, Fld2, Fld3, Fld4, 0 AS Late
    FROM Table01
    WHERE xDate = CONVERT(date, '20240101', 112)
),
P02 AS (
    SELECT Fld1, Fld2, Fld3, Fld4, 1 AS Late
    FROM Table01
    WHERE xDate < CONVERT(date, '20241201', 112)
)
-- 合并结果，需去重用UNION，保留全部记录用UNION ALL
SELECT * FROM P01
UNION ALL
SELECT * FROM P02;

场景2：基于共同字段关联（JOIN） #

如果你的需求是通过共同字段（比如Fld1）将两个数据集的字段关联，获取匹配的组合记录：

WITH P01 AS (
    SELECT Fld1, Fld2, Fld3, Fld4
    FROM Table01
    WHERE xDate = CONVERT(date, '20240101', 112)
),
P02 AS (
    SELECT Fld1, Fld2 AS P02_Fld2, Fld3 AS P02_Fld3, Fld4 AS P02_Fld4, 1 AS Late
    FROM Table01
    WHERE xDate < CONVERT(date, '20241201', 112)
)
-- 用INNER JOIN取双向匹配记录，如需保留P01全部记录可用LEFT JOIN
SELECT 
    p1.Fld1, p1.Fld2, p1.Fld3, p1.Fld4,
    p2.P02_Fld2, p2.P02_Fld3, p2.P02_Fld4, p2.Late
FROM P01 p1
INNER JOIN P02 p2 ON p1.Fld1 = p2.Fld1;

补充说明 #

• 如果不需要复用数据集，也可以直接用子查询替代CTE，示例：

  SELECT * FROM (
      SELECT Fld1, Fld2, Fld3, Fld4, 0 AS Late
      FROM Table01
      WHERE xDate = CONVERT(date, '20240101', 112)
  ) P01
  UNION ALL
  SELECT * FROM (
      SELECT Fld1, Fld2, Fld3, Fld4, 1 AS Late
      FROM Table01
      WHERE xDate < CONVERT(date, '20241201', 112)
  ) P02;
  

• UNION会自动去重重复记录，UNION ALL保留所有原始记录，请根据业务需求选择。

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