ANALITIC与INTRAT表关联查询问题及SQL语句修正需求

背景说明 #

• 涉及两张数据表：ANALITIC、INTRAT
• ANALITIC表字段：
  • REGISTRATION_DATE：注册日期（格式DD/MM/YYYY）
  • REGISTRATION_NUMBER：注册编号（同一编号可对应多个不同日期）
  • REGISTRATION_CODE：注册编码
• 核心需求：
  筛选ANALITIC中REGISTRATION_CODE为'222222'且注册日期在2023年（2023-01-01至2023-12-31）的记录，关联INTRAT表获取同一注册编号、同一注册日期对应的INTRAT_BILL、INTRAT_VALUE字段（INTRAT中对应记录可能为0条、1条或多条）
• 当前问题：原有仅通过REGISTRATION_DATE做LEFT JOIN的SQL返回了不属于当前编号的旧日期数据，需修正

原有错误SQL #

SELECT
    a.REGISTRATION_NUMBER
    a.REGISTRATION_DATE
    a.REGISTRATION_CODE
    i.INTRAT_BILL
    i.INTRAT_VALUE
FROM
    ANALITIC a
LEFT JOIN
    INTRAT i
ON
    a.REGISTRATION_DATE = i.REGISTRATION_DATE
WHERE
    a.REGISTRATION_DATE BETWEEN '20230101' and '20231231' AND
    a.REGISTRATION_CODE = '222222'
ORDER BY
    a.REGISTRATION_DATE, a.REGISTRATION_NUMBER;

问题根源 #

1. JOIN条件不完整：仅匹配日期字段，会将ANALITIC中某编号某日期的记录，与INTRAT中所有同日期但不同编号的记录错误关联，导致混入其他编号的旧数据
2. 日期格式不匹配：ANALITIC的日期格式是DD/MM/YYYY，而WHERE条件中用的是'20230101'（YYYYMMDD格式）的字符串匹配，会导致日期判断错误（比如'01/02/2023'会被误判为小于'20230101'）

修正后的SQL #

MySQL版本 #

SELECT
    a.REGISTRATION_NUMBER,
    a.REGISTRATION_DATE,
    a.REGISTRATION_CODE,
    i.INTRAT_BILL,
    i.INTRAT_VALUE
FROM
    ANALITIC a
LEFT JOIN
    INTRAT i
ON
    -- 同时关联注册编号和日期，确保匹配同一编号同一日期的记录
    a.REGISTRATION_NUMBER = i.REGISTRATION_NUMBER
    AND a.REGISTRATION_DATE = i.REGISTRATION_DATE
WHERE
    -- 将字符串日期转换为日期类型后再判断范围，避免格式不匹配导致的错误
    STR_TO_DATE(a.REGISTRATION_DATE, '%d/%m/%Y') BETWEEN '2023-01-01' AND '2023-12-31'
    AND a.REGISTRATION_CODE = '222222'
ORDER BY
    a.REGISTRATION_DATE, a.REGISTRATION_NUMBER;

Oracle版本 #

SELECT
    a.REGISTRATION_NUMBER,
    a.REGISTRATION_DATE,
    a.REGISTRATION_CODE,
    i.INTRAT_BILL,
    i.INTRAT_VALUE
FROM
    ANALITIC a
LEFT JOIN
    INTRAT i
ON
    a.REGISTRATION_NUMBER = i.REGISTRATION_NUMBER
    AND a.REGISTRATION_DATE = i.REGISTRATION_DATE
WHERE
    TO_DATE(a.REGISTRATION_DATE, 'DD/MM/YYYY') BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
    AND a.REGISTRATION_CODE = '222222'
ORDER BY
    a.REGISTRATION_DATE, a.REGISTRATION_NUMBER;

关键修正点 #

• 完善JOIN条件：同时关联REGISTRATION_NUMBER和REGISTRATION_DATE，确保仅匹配同一编号、同一日期的INTRAT记录
• 统一日期处理：将字符串格式的日期转换为数据库可识别的日期类型后再进行范围判断，避免字符串匹配导致的逻辑错误

内容的提问来源于stack exchange，提问作者N.TheQuick