mysql - 将多行地址分组以仅显示最新行

Question

我正在使用以下 SQL 从我的数据库表 check_in 获取信息

SELECT checkin_id, checkin_client_id, checkin_inventory_id, checkin_property_id, checkin_date, client_username, property_address_line_1, property_town, property_county, property_postcode, property_type, property_rooms, client_first_name, client_last_name, client_organisation_name, client_unique_id, checkin_passcode
FROM check_in ci
INNER JOIN properties pr
    ON ci.checkin_property_id = pr.property_id
INNER JOIN clients cl
    ON ci.checkin_client_id = cl.client_id
WHERE client_unique_id LIKE ? OR client_first_name LIKE ? OR client_username LIKE ? OR client_organisation_name LIKE ? OR property_address_line_1 LIKE ?

效果很好，它会返回与搜索词匹配的所有行，但是，结果可能会具有链接到它们的相同地址，但日期较新(checkin_date) 。我只想显示每个地址的最新行 (property_address_line_1)，执行此操作的最佳方法是什么？

Answer 1

请参阅下文，我添加了一个子查询来将结果限制为每个酒店的最新入住情况。

SELECT checkin_id,
       checkin_client_id,
       checkin_inventory_id,
       checkin_property_id,
       checkin_date,
       client_username,
       property_address_line_1,
       property_town,
       property_county,
       property_postcode,
       property_type,
       property_rooms,
       client_first_name,
       client_last_name,
       client_organisation_name,
       client_unique_id,
       checkin_passcode
  FROM check_in ci
 INNER JOIN properties pr
    ON ci.checkin_property_id = pr.property_id
 INNER JOIN clients cl
    ON ci.checkin_client_id = cl.client_id
 WHERE checkin_date =
       (select max(ci2.checkin_date)
          from check_in ci2
         where ci2.checkin_property_id = ci.checkin_property_id)
   and (client_unique_id LIKE ? OR client_first_name LIKE ? OR
       client_username LIKE ? OR client_organisation_name LIKE ? OR
       property_address_line_1 LIKE ?)