如何关联日期格式不同的两张SQL表（YYYYMMDD与YYYYMM）

嘿，针对你这个表关联的需求，我整理了具体的实现方法，咱们一步步来：

需求说明 #

我们需要关联Table1和Table2，核心规则是：

• 首先按Product字段完全匹配
• Table1的日期（YYYYMMDD格式）必须落在Table2日期（YYYYMM格式，代表当月最后一个工作日）对应的月份内
• 最终只保留每个Product对应月份里，Table1中日期最晚的那条记录，同时关联Table2的Country字段

原始表结构 #

Table1 #

Product Date       State
A       20080107   NY
A       20080131   TX
B       20100212   CT
B       20100226   MT
C       20150312   HG
C       20140425   UP

Table2 #

Product Date   Country
A       200801 USA
C       201503 AUS
B       201002 UK
B       201704 FIN
C       200605 IRE
A       200805 CAN

期望输出 #

Product Date       State Country
A       20080131   TX    USA
B       20100226   MT    UK

实现SQL（以MySQL为例，其他数据库可稍作调整） #

SELECT t1.Product, t1.Date, t1.State, t2.Country
FROM Table1 t1
JOIN Table2 t2 
  ON t1.Product = t2.Product
  -- 把Table1的Date转成YYYYMM格式，和Table2的Date匹配
  AND DATE_FORMAT(STR_TO_DATE(t1.Date, '%Y%m%d'), '%Y%m') = t2.Date
-- 子查询筛选每个Product对应月份里的最晚日期记录
WHERE (t1.Product, t1.Date) IN (
    SELECT Product, MAX(Date)
    FROM Table1
    GROUP BY Product, DATE_FORMAT(STR_TO_DATE(Date, '%Y%m%d'), '%Y%m')
)
-- 只保留在Table2中有对应月份匹配的记录
AND EXISTS (
    SELECT 1 
    FROM Table2 t2_sub
    WHERE t2_sub.Product = t1.Product
    AND DATE_FORMAT(STR_TO_DATE(t1.Date, '%Y%m%d'), '%Y%m') = t2_sub.Date
);

代码解释 #

1. 日期格式转换：用STR_TO_DATE把Table1的数字型日期转成日期类型，再用DATE_FORMAT提取YYYYMM格式，和Table2的Date字段匹配，实现月份关联。
2. 筛选最晚记录：通过子查询按Product和月份分组，取出每组的最大日期，确保只保留每个产品对应月份里的最新记录。
3. 存在性校验：用EXISTS确保最终的记录在Table2中有对应的月份匹配，避免出现无关联的无效数据。

如果是其他数据库（比如SQL Server），日期转换函数会略有不同，比如用CONVERT和LEFT函数来提取月份：

-- SQL Server版本示例
SELECT t1.Product, t1.Date, t1.State, t2.Country
FROM Table1 t1
JOIN Table2 t2 
  ON t1.Product = t2.Product
  AND LEFT(t1.Date, 6) = t2.Date
WHERE (t1.Product, t1.Date) IN (
    SELECT Product, MAX(Date)
    FROM Table1
    GROUP BY Product, LEFT(Date, 6)
)
AND EXISTS (
    SELECT 1 
    FROM Table2 t2_sub
    WHERE t2_sub.Product = t1.Product
    AND LEFT(t1.Date, 6) = t2_sub.Date
);

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