Oracle同表不同日期数据关联查询及去重问题

现有一张Sales_Table表，包含Cust_Id、Sales、Sale_Date字段，存储了2021年和2022年的销售数据。需要将两年数据按月份匹配关联，展示成指定格式，但当前用左连接编写的SQL出现了重复值，求正确的查询写法。

原始数据示例 #

-- 2021年数据
custID_1| sales_1 |sales_date_1| 
01      | 100     |01/2021     | 
02      | 102     |02/2021     | 
07      | 10      |04/2021     | 
10      | 180     |05/2021     | 
12      | 90      |06/2021     | 

-- 2022年数据
custID_2| sales_2 |sales_date_2| 
05      | 400     |02/2022     | 
06      | 110     |03/2022     | 
08      | 300     |04/2022     | 
11      | 80      |06/2022     | 

期望结果 #

custID_1| custID_2 |sales_1 |sales_2 |sales_date_1|sales_date_2
01      | null     |100     |null    |01/2021     |null    
02      | 05       |102     |400     |02/2021     |02/2022
null    | 06       |null    |110     |null        |03/2022
07      | 08       |10      |300     |04/2021     |04/2022  
10      | null     |180     |null    |05/2021     |null        
12      | 11       |90      |80      |06/2021     |06/2022

当前尝试的SQL（出现重复值） #

Select S1.Cust_Id "custID_1", s2.Cust_Id "custID_2",
s1.Sales "sales_1", s2.Sales "sales_2",
s1.Sale_Date "sales_date_1", s2.Sale_Date "sales_date_2"
From Sales_Table S1 Left Join Sales_Table S2
On S1.Cust_Id = S2.Cust_Id
And S1.Sale_Date Between '01-01-21' And '31-10-21'
And S2.Sale_Date Between '01-01-22' And '31-10-22'

错误原因分析 #

当前SQL的核心问题是关联逻辑错误：

• 用Cust_Id作为关联键，但需求是按月份匹配，不是按客户ID匹配
• 日期范围条件放在ON子句中，会导致左连接时保留不符合日期条件的记录，进而产生重复或不符合预期的关联结果

正确解法 #

方案1：使用全外连接（支持的数据库：PostgreSQL、SQL Server等） #

先分别提取两年的数据，以**月份（不带年份）**作为关联键，用FULL OUTER JOIN保留两边所有月份的数据：

SELECT 
    s1.Cust_Id AS custID_1,
    s2.Cust_Id AS custID_2,
    s1.Sales AS sales_1,
    s2.Sales AS sales_2,
    s1.Sale_Date AS sales_date_1,
    s2.Sale_Date AS sales_date_2
FROM (
    -- 筛选2021年数据，提取月份作为关联键
    SELECT Cust_Id, Sales, Sale_Date, LEFT(Sale_Date, 2) AS month_key
    FROM Sales_Table
    WHERE Sale_Date LIKE '%/2021'
      AND Sale_Date BETWEEN '01/2021' AND '10/2021'
) s1
FULL OUTER JOIN (
    -- 筛选2022年数据，提取相同格式的月份作为关联键
    SELECT Cust_Id, Sales, Sale_Date, LEFT(Sale_Date, 2) AS month_key
    FROM Sales_Table
    WHERE Sale_Date LIKE '%/2022'
      AND Sale_Date BETWEEN '01/2022' AND '10/2022'
) s2 ON s1.month_key = s2.month_key
-- 按月份排序，和期望结果一致
ORDER BY COALESCE(s1.month_key, s2.month_key);

方案2：MySQL兼容写法（MySQL不支持FULL OUTER JOIN） #

用UNION ALL结合左连接，模拟全外连接的效果：

-- 先取2021年所有数据，关联2022年对应月份的数据
SELECT 
    s1.Cust_Id AS custID_1,
    s2.Cust_Id AS custID_2,
    s1.Sales AS sales_1,
    s2.Sales AS sales_2,
    s1.Sale_Date AS sales_date_1,
    s2.Sale_Date AS sales_date_2
FROM (
    SELECT Cust_Id, Sales, Sale_Date, LEFT(Sale_Date, 2) AS month_key
    FROM Sales_Table
    WHERE Sale_Date LIKE '%/2021'
      AND Sale_Date BETWEEN '01/2021' AND '10/2021'
) s1
LEFT JOIN (
    SELECT Cust_Id, Sales, Sale_Date, LEFT(Sale_Date, 2) AS month_key
    FROM Sales_Table
    WHERE Sale_Date LIKE '%/2022'
      AND Sale_Date BETWEEN '01/2022' AND '10/2022'
) s2 ON s1.month_key = s2.month_key

UNION ALL

-- 再取2022年中2021年没有对应月份的数据
SELECT 
    NULL AS custID_1,
    s2.Cust_Id AS custID_2,
    NULL AS sales_1,
    s2.Sales AS sales_2,
    NULL AS sales_date_1,
    s2.Sale_Date AS sales_date_2
FROM (
    SELECT Cust_Id, Sales, Sale_Date, LEFT(Sale_Date, 2) AS month_key
    FROM Sales_Table
    WHERE Sale_Date LIKE '%/2022'
      AND Sale_Date BETWEEN '01/2022' AND '10/2022'
) s2
WHERE NOT EXISTS (
    SELECT 1 
    FROM Sales_Table s1
    WHERE s1.Sale_Date LIKE '%/2021' 
      AND LEFT(s1.Sale_Date, 2) = s2.month_key
)
-- 按月份排序
ORDER BY COALESCE(LEFT(sales_date_1, 2), LEFT(sales_date_2, 2));

说明 #

• 关联键用月份（前两位字符），确保同一月份的两年数据能正确匹配
• 日期筛选条件放在子查询中，避免关联时引入无效数据
• 用COALESCE统一排序逻辑，保证结果按月份顺序展示

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