mysql - 选择每组最近的日期，但必须在连续的日期进行分组

Question

如何按一列值对相邻行进行分组，以便仅返回这些分组行中的最新行？

travel 表中的数据

id   name    location    date
--------------------------------------------
1    name    London      2018-09-01 00:00:00
2    name    London      2018-09-02 00:00:00
3    name    NYC         2018-09-03 00:00:00
4    name    London      2018-09-04 00:00:00
5    name    NYC         2018-09-05 00:00:00
6    name    NYC         2018-09-06 00:00:00
7    name    Tokyo       2018-09-07 00:00:00

结果(顺序颠倒)

7    name    Tokyo       2018-09-07 00:00:00
6    name    NYC         2018-09-06 00:00:00
4    name    London      2018-09-04 00:00:00
3    name    NYC         2018-09-03 00:00:00
2    name    London      2018-09-02 00:00:00

可以玩的 fiddle :http://sqlfiddle.com/#!9/02033ba/15

Answer 1

使用双 NOT EXISTS。简单地说，选择除将来存在相同位置且两者之间不存在不同位置的行之外的所有行。

SELECT *
FROM travel curr
WHERE NOT EXISTS (
    SELECT 1
    FROM travel next
    WHERE next.name = curr.name
    AND next.location = curr.location
    AND next.date > curr.date
    AND NOT EXISTS (
        SELECT 1
        FROM travel inbw
        WHERE inbw.name = curr.name
        AND inbw.location <> curr.location
        AND inbw.date > curr.date
        AND inbw.date < next.date
    )
)
ORDER BY date DESC

Revised SQL Fiddle