MYSQL: 根据user_id分组，找到离指定日期最近的日期

Question

我需要获取多行数据，这些数据的date_added日期最接近但不晚于用户提供的日期，并按user_id分组。

我查看了许多关于在分组中使用max的答案，但还没有达到目的： 获取与指定日期最接近的记录并按类型分组 SQL查询以显示最接近的日期？ 在mysql查询中查找最接近的日期时间 从MySQL表中获取最接近的日期

https://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

这很接近：https://dev59.com/UmQn5IYBdhLWcg3wLkkd#17038667。但是它找到的是最大日期，我需要根据用户输入指定的最大值，而不是绝对的最大值。

以下是已过滤出正确组织ID、框架ID和级别ID的数据子集。

+----+---------+-----------------+--------------+----------+---------------------+
| id | user_id | organisation_id | framework_id | level_id |     date_added      |
+----+---------+-----------------+--------------+----------+---------------------+
|  2 |       1 |               2 |            1 |        1 | 2015-07-31 14:02:49 |
|  9 |       2 |               2 |            1 |        1 | 2015-09-01 11:05:09 |
| 11 |       1 |               2 |            1 |        1 | 2015-09-07 14:13:39 |
+----+---------+-----------------+--------------+----------+---------------------+

如果提供的日期是2015年9月7日，我期望看到id为9和11。

如果提供的日期是2015年9月1日，我期望看到id为2和9。

如果提供的日期是2015年7月31日，我期望看到id为2。

这个查询是我所能得到的最接近的结果：

SELECT  t1.id
    , t1.user_id
    , t1.date_added 
FROM 
    completed_frameworks AS t1
WHERE date_added = (
    SELECT 
        MAX(date_added)
    FROM 
        completed_frameworks
    WHERE 
        user_id = t1.user_id
        AND 
        date_added <= '2015-09-07 23:59:59'
)
AND
(
    t1.organisation_id = 2 
    AND 
    t1.framework_id = 1 
    AND 
    t1.level_id = 1
)

它返回了我对于日期2015-09-07的预期结果。

然而，当日期为2015-09-01时，它仅返回id 9。我也希望得到id 2的返回结果。

当日期为2015-07-31时，它返回0行。

如果需要其他信息，请告诉我。

谢谢！

编辑：

感谢迄今为止的回复。我需要澄清两点：

1）我没有限制。由于用户ID，我期望返回那些行。可能会返回n个用户。我只想要每个用户的一行，其中date_added最接近用户提供的日期。

2）提供的日期将不具有时间值。它将来自简单的日期选择器UI。在我的示例查询中，我添加了23:59:59的时间，以包含当天的所有内容。

- LGH

Answer 1

尝试使用此子查询。将组织ID / 框架ID / 级别ID过滤器移至子查询中，现在它返回了你提供的示例的正确值。

SELECT t1.id, t1.user_id, t1.date_added 
FROM 
    completed_frameworks AS t1
WHERE date_added = (
    SELECT 
        MAX(date_added)
    FROM 
        completed_frameworks
    WHERE 
        user_id = t1.user_id
    AND 
        date_added <= '2015-09-01 23:59:59'
    AND
        organisation_id = 2 
    AND 
        framework_id = 1 
    AND 
        level_id = 1
)

- David Ritchie

---

我假设你提供的子集数据并不是全部数据（我猜“子集”这个词就是提示），如果是这样，那么子查询可能会返回一些数据，但最终会被“WHERE”语句过滤掉（例如，如果最近添加的记录是针对其他框架或级别的）。 - David Ritchie

Answer 2

这里是一个样例查询，适用于输入日期为'2015-09-07 14:13:39'的情况。内部查询返回输入日期以及下一个最高日期。然后使用这个临时表来过滤completed_frameworks，以便只获取距离输入日期最近的两个日期的记录。

SELECT t1.id, t1.user_id, t1.date_added
FROM completed_frameworks t1
INNER JOIN
(
    SELECT cf.date_added
    FROM completed_frameworks cf
    GROUP BY cf.date_added
    HAVING cf.date_added <= '2015-09-07 14:13:39'
    ORDER BY cf.date_added DESC
    LIMIT 2
) t2
ON t1.date_added = t2.date_added
WHERE t1.organisation_id = 2 AND t1.framework_id = 1 AND t1.level_id = 1

- Tim Biegeleisen

Answer 3

我认为这个可以满足你的需求：

SELECT f.id, f.user_id  f.date_added 
FROM completed_frameworks f
WHERE date(f.date_added) <= '2025-09-07'  -- or whatever your date is
ORDER BY f.date_added DESC
LIMIT 2;

编辑：

如果您想要每个用户ID最接近指定日期的一个日期，我建议使用以下方法：

select f.*
from completed_frameworks f join
     (select user_id, max(date_added) as maxda
      from completed_frameworks
      where date(date_added) <= '2015-09-07'
      group by user_id
     ) u
     on f.user_id = u.user_id and f.date_added = maxda

- Gordon Linoff

---

你比我更了解，但我担心他的实际数据集可能会有多个记录在同一日期。 - Tim Biegeleisen

@TimBiegeleisen……我不确定这与此有什么关系。这将检索最近的两个条目。 - Gordon Linoff

谢谢大家。我已经编辑了问题以获得更大的清晰度。没有限制。预期结果是由用户ID确定的。我不是在寻找最近的两个。 - LGH

@LGH...哦，那个问题非常不同。 - Gordon Linoff