MySQL查询：筛选指定时间内有效的社工-服务对象关联记录

现有两张表：

• relationships表：包含person_a、person_b、start_date、end_date、relationship等字段
• relationship_type表：包含relationship_type_id、uuid等字段

业务场景：服务对象对应person_a，会被分配社工（对应person_b）；关联关系要么有完整的起止时间段，要么仅设置了开始日期（无结束日期）。

目标是编写MySQL查询，获取指定日期（2022-10-26）内有效的关联记录，返回服务对象的patient_id（即person_a）和社工姓名。

原查询语句如下：

SELECT r.person_a AS patient_id, greatest((r.start_date), (r.end_date)) as relationship_date,
concat_ws( ' ', pn.family_name, pn.given_name, pn.middle_name ) AS NAME
FROM
relationship r 
INNER JOIN relationship_type t ON r.relationship = t.relationship_type_id
INNER JOIN person_name pn ON r.person_b = pn.person_id 
WHERE
t.uuid = '9065e3c6-b2f5-4f99-9cbf-f67fd9f82ec5' 
AND (
r.end_date IS NULL 
OR r.end_date <= date("2022-10-26"));

当前查询结果不符合预期，需要优化。

你的查询存在两个核心问题：有效时间范围判断错误、relationship_date字段逻辑错误，修正如下：

1. 修正有效关联的判断逻辑 #

要确保2022-10-26处于关联的有效时间段内，必须同时满足两个条件：

• 关联的开始日期不晚于指定日期：r.start_date <= '2022-10-26'
• 关联未结束：要么无结束日期（r.end_date IS NULL），要么结束日期不早于指定日期（r.end_date >= '2022-10-26'）

原查询中r.end_date <= '2022-10-26'会把已经过期的关联也纳入结果，这是错误的。

2. 修正relationship_date字段的取值逻辑 #

需求是“无结束日期时使用开始日期”，这里应该用IFNULL函数：当end_date不为空时取它，为空则取start_date。原查询用GREATEST的问题在于，当end_date为NULL时，GREATEST(start_date, NULL)的结果是NULL，完全不符合需求。

最终优化后的查询语句 #

SELECT 
    r.person_a AS patient_id,
    IFNULL(r.end_date, r.start_date) AS relationship_date,
    CONCAT_WS(' ', pn.family_name, pn.given_name, pn.middle_name) AS NAME
FROM
    relationship r
INNER JOIN relationship_type t ON r.relationship = t.relationship_type_id
INNER JOIN person_name pn ON r.person_b = pn.person_id
WHERE
    t.uuid = '9065e3c6-b2f5-4f99-9cbf-f67fd9f82ec5'
    AND r.start_date <= '2022-10-26'
    AND (r.end_date IS NULL OR r.end_date >= '2022-10-26');

补充说明 #

如果业务中对“有效关联”有特殊定义（比如允许开始日期晚于指定日期的预分配关联），可以根据实际需求调整start_date的判断条件。

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