mysql - 仅当 ID 匹配时返回最大连接结果

Question

我正在尝试构建一个查询，以选择出租房的所有租户。每个属性可以有多个租户，每个租户可以移动到不同的属性。

用户属性表(的一部分)如下所示:

User ID  |  Property ID  |  Move In Date  |  Move Out Date
----------------------------------------------------------
224      |  33A          |  2015-11-01    |  NULL
224      |  36B          |  2015-11-15    |  NULL
226      |  33A          |  2015-11-01    |  NULL

在上面的示例中，用户 224 在 1 日搬进了属性(property) 33A，然后在 15 日搬进了属性(property) 36B。对于这个问题，例如，我假设 move_out_date 列没有正确填充日期。在这种情况下，我需要一个可以绕过该问题的查询。

到目前为止，这是我的查询内容:

SELECT
    u.user_email, p.user_fname, p.user_lname, r.move_in_date AS occupant_date
FROM
    user u
LEFT JOIN
    user_profile p ON p.user_id = u.user_id
LEFT JOIN
    (
        SELECT
            user_id, property_id, move_in_date
        FROM
            user_property
        WHERE
            move_out_date IS NULL
        ORDER BY
            move_in_date DESC
    ) r ON r.user_id = u.user_id
WHERE
    r.property_id = '33A'
GROUP BY
    u.user_id
ORDER BY
    r.move_in_date ASC, p.user_fname ASC

此查询返回每个居住者的信息和入住日期。对于属性 33A，它返回用户 224 和 226，但我只想返回用户 226，因为从技术上讲，用户224 从该属性移动。

目前输出表是这样的:

User ID  |  Property ID  |  Move In Date  |  Move Out Date
----------------------------------------------------------
224      |  33A          |  2015-11-01    |  NULL
226      |  33A          |  2015-11-01    |  NULL

User ID  |  Email        |  First Name  |  Last Name  | Occupant Date
---------------------------------------------------------------------
224      |  user1@e.com  |  Kevin       |  Doe        |  2015-11-01
226      |  user2@e.com  |  Tom         |  Smith      |  2015-11-01

但我只想要用户 226 的结果，特别是属性 ID 33A。最终，这...

User ID  |  Email        |  First Name  |  Last Name  | Occupant Date
---------------------------------------------------------------------
226      |  user2@e.com  |  Tom         |  Smith      |  2015-11-01

这是否可以在一个查询中完成，或者我需要做几个吗？感谢您对此提供的任何帮助!

Answer 1

获取给定属性的当前租户的一种方法:

select p.user_id, p.property_id, p.move_in_date from
user_property p
join (
    -- Get users most recent move in date.
    select max(move_in_date) move_in_date, user_id
    from   user_property
    group by user_id
) u_move_in on (p.user_id = u_move_in.user_id and p.move_in_date = u_move_in.move_in_date)
join (
    -- Get the property's most recent move in date.
    select max(move_in_date) move_in_date, property_id
    from   user_property
    where  property_id = '33A'
    group by property_id
) p_move_in on (p.property_id = p_move_in.property_id and p.move_in_date = p_move_in.move_in_date);

查找日期最近移动的用户 = 属性最近移动日期。