SQL显示所有匹配客户端id的结果，但只显示每个实例的最新版本。

i have the following sql code that displays all records that match the user SPI-GVT97. It displays 3 results as you can see. I'd like it to show the most recent version of an instance if more than one exists. So the most recent version of summers place is 2014-03-08 and as they is only one instance of 26 friars view, that should still show. So ideally, only 2 results would show instead of the current 3. I have tried max(checkin_date) but it just does the most recent version of everything for that client code.

我有以下sql代码，它显示与用户SPI-GVT97匹配的所有记录。它显示3个结果，您可以看到。我希望它显示一个实例的最新版本，如果存在多个实例的话。因此，最新的夏季版本是2014-03-08，因为它们只是26个friars视图的一个实例，这应该还会显示出来。所以理想情况下，只有两个结果会显示而不是当前的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 ?

1 个解决方案

#1

Append this condition at the end of the query

在查询的末尾附加此条件。

........
........
........
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
)

---

EDIT

编辑

---

It's hard to say looking only at the query and without knowing tables structures.
A general approach using NOT EXIST works fine, look at this demo: http://sqlfiddle.com/#!2/9f99d/1

很难说只查看查询和不了解表结构。使用不存在的一般方法很好，看看这个演示: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 |