基于实际日期的SQL内连接条件：筛选指定日期的有效证件

表结构 #

CREATE TABLE people 
(
    p_id INT NOT NULL AUTO_INCREMENT,
    p_name VARCHAR(50) NOT NULL,
    PRIMARY KEY(p_id)
);

INSERT INTO people (p_id, p_name)
VALUES (1, 'Name_1'),
       (2, 'Name_2');

CREATE TABLE documents 
(
    d_id INT NOT NULL AUTO_INCREMENT,
    d_people INT(10) NOT NULL,
    d_date VARCHAR(10) NOT NULL,
    PRIMARY KEY(d_id)
);

INSERT INTO documents (d_id, d_people, d_date)
VALUES (1, 1, '01.01.2022'),
       (2, 2, '01.12.2021'),
       (3, 1, '05.02.2022'),
       (4, 1, '10.02.2022'),
       (5, 2, '04.01.2022'),
       (6, 1, '20.01.2022');

查询需求 #

当指定日期为21.01.2022时，筛选出p_id=1的用户的有效证件（需返回d_id=6的记录），补全以下SQL语句的日期条件：

SELECT * 
FROM people
INNER JOIN documents ON d_people = p_id
WHERE p_id = 1 AND ??? d_date 21.01.2022 ???
;

解决方案 #

由于d_date是VARCHAR类型的dd.mm.yyyy格式字符串，直接做字符串比较会出现逻辑错误（比如05.02.2022字符串比21.01.2022大，但实际日期更早），必须先转换成日期类型再进行比较。

方式1：筛选后取最新记录 #

这是最直接的写法，先筛选出符合日期条件的记录，再按日期倒序取第一条：

SELECT * 
FROM people
INNER JOIN documents ON d_people = p_id
WHERE p_id = 1 
  AND STR_TO_DATE(d_date, '%d.%m.%Y') <= STR_TO_DATE('21.01.2022', '%d.%m.%Y')
ORDER BY STR_TO_DATE(d_date, '%d.%m.%Y') DESC
LIMIT 1;

方式2：子查询定位最新有效日期 #

如果需要更严谨地匹配唯一的目标行，可以通过子查询先找到该用户的最大有效日期，再关联查询：

SELECT p.*, d.*
FROM people p
INNER JOIN documents d ON d.d_people = p.p_id
WHERE p.p_id = 1
  AND STR_TO_DATE(d.d_date, '%d.%m.%Y') = (
    SELECT MAX(STR_TO_DATE(d_date, '%d.%m.%Y'))
    FROM documents
    WHERE d_people = 1
      AND STR_TO_DATE(d_date, '%d.%m.%Y') <= STR_TO_DATE('21.01.2022', '%d.%m.%Y')
  );

关键说明 #

• STR_TO_DATE(d_date, '%d.%m.%Y')是MySQL中把dd.mm.yyyy格式字符串转成日期类型的函数，确保日期比较逻辑准确。
• 两种写法都能精准返回d_id=6的记录，因为这条记录是p_id=1用户中，日期不晚于21.01.2022的最新证件。

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