mysql - 从连接表中选择最大值

Question

我需要有关 mysql 查询的帮助。我的表:

对象

+---------+--------+
| id      | name   |
+---------+--------+
|    1    | house 1|
|    2    | house 2|
|    3    | house 3|
+---------+--------+

对象过期

+----------+-----------+
| object_id| expire    |
+----------+-----------+
|    1     | 2014-09-11|
|    1     | 2015-09-11|
|    2     | 2014-09-11|
|    2     | 2015-09-11|
|    2     | 2016-09-11|
|    3     | 2013-09-11|
|    3     | 2014-09-11|
|    3     | 2015-09-15|
+----------+-----------+

现在我需要最大“过期”大于 2015-09-04 小于 2015-09-18(+/- 7 天)的对象

喜欢这个结果:

+----------+-----------+-----------+
| object_id| expire    | name      |
+----------+-----------+-----------+
|    1     | 2015-09-11| house 1   |
|    3     | 2015-09-15| house 3   |
+----------+-----------+-----------+

这是我现在拥有的:

SELECT o.id, MAX(uio.expire) AS object_expires
FROM objects AS o
LEFT JOIN objects_expire AS oe ON oe.object_id = o.id
WHERE expire < '2015-09-18'
AND expires > '2015-09-04'
GROUP BY o.id

但那是不正确的。

感谢您的帮助!!!

Answer 1

一种常用的方法是先进行分组，然后再加入，如果您不想硬编码日期，您可以随时使用 date_sub 和 date_add 函数来从当前日期开始 -/+ 7 天。

select 
o.id,
e.mexpire as expire,
o.name
from objects o
join(
  select object_id,max(expire) as mexpire
  from objects_expire
  group by object_id
  having mexpire > date_sub(curdate(),interval 7 day) and mexpire < date_add(curdate(),interval 7 day)
)e
on o.id = e.object_id