php - SQL 显示与客户端 ID 匹配的所有结果，但仅显示每个实例的最新版本

Question

我有以下sql代码，显示与用户SPI-GVT97匹配的所有记录。如您所见，它显示 3 个结果。如果存在多个实例，我希望它显示实例的最新版本。因此 Summers Place 的最新版本是 2014-03-08，并且由于它们只是 26 friars View 的一个实例，因此应该仍会显示。因此，理想情况下，只会显示 2 个结果，而不是当前的 3 个结果。我已尝试过 max(checkin_date)，但它只执行该客户端代码的所有内容的最新版本。

SELECT checkin_id, 
       checkin_client_id, 
       checkin_inventory_id, 
       checkin_property_id, 
       checkin_date, 
       property_address_line_1, 
       client_first_name, 
       client_last_name, 
       client_organisation_name, 
       client_unique_id 
  FROM check_in 
       INNER JOIN properties 
           ON checkin_property_id = property_id 
       INNER JOIN clients 
           ON checkin_client_id = client_id 
 WHERE client_unique_id LIKE ?

Answer 1

将此条件附加到查询末尾

........
........
........
WHERE client_unique_id LIKE ?

  AND NOT EXISTS (
    SELECT 1 FROM check_in CC
    WHERE check_in.checkin_client_id = CC.checkin_client_id
      AND CC.checkin_date < check_in.checkin_date
)

<小时/>

编辑

<小时/>

很难说只看查询而不了解表结构。
使用 NOT EXIST 的一般方法效果很好，请看这个演示:http://sqlfiddle.com/#!2/9f99d/1

create table clients(
  client_id int primary key
);

insert into clients values(1),(2),(3);

create table check_in(
  check_in_id int primary key auto_increment,
  checkin_client_id int,
  checkin_date date
);

insert into check_in( checkin_client_id, checkin_date )
values
(1,'2014-02-01'),(1,'2014-02-03'),
(2,'2010-02-01'),(2,'2014-02-01'),(2,'2014-02-15'),
(3,'2010-12-01'),(2,'2014-01-21'),(2,'2014-02-10');

select *
from clients c
join check_in ci
on c.client_id = ci.checkin_client_id
where NOT EXISTS (
  select 1 
  from check_in cc1
  where ci.checkin_client_id = cc1.checkin_client_id
    and ci.checkin_date < cc1.checkin_date
)

| CLIENT_ID | CHECK_IN_ID | CHECKIN_CLIENT_ID |                    CHECKIN_DATE |
|-----------|-------------|-------------------|---------------------------------|
|         1 |           2 |                 1 | February, 03 2014 00:00:00+0000 |
|         2 |           5 |                 2 | February, 15 2014 00:00:00+0000 |
|         3 |           8 |                 3 | February, 10 2014 00:00:00+0000 |