sql - 如何在过滤后订购有很多关系

Question

我有 users locations 和一个连接表 visits。每个用户都可以访问多个位置，我想查看按最近访问位置的名称排序的用户。

CREATE TABLE users
  ("id" int, "name" varchar(128))
;

CREATE TABLE locations
  ("id" int, "name" varchar(128))
;

CREATE TABLE visits
  ("id" int, "user_id" int, "location_id" int, "date" timestamp)
;

INSERT INTO users
    ("id", "name")
VALUES
    (1, 'Alpha'),
    (2, 'Bravo'),
    (3, 'Charlie');

INSERT INTO locations
    ("id", "name")
VALUES
    (4, 'Delta'),
    (5, 'Echo'),
    (6, 'Foxtrott');

INSERT INTO visits
  ("id", "user_id", "location_id", "date")
VALUES
  (1, 1, 4, '2000-02-03 00:00:00'),
  (2, 1, 5, '2000-02-02 00:00:00'),
  (3, 1, 6, '2000-02-01 00:00:00'),
  (4, 2, 6, '2000-01-01 00:00:00'),
  (5, 2, 5, '2000-01-01 00:00:00')
;

我试过了

SELECT users.id, users.name, max(locations.name) as location_name, max(visits.date) as date
FROM users
LEFT JOIN visits ON users.id = visits.user_id
LEFT JOIN locations ON visits.location_id = locations.id
GROUP BY users.id, users.name
ORDER BY location_name

但是 max(locations.name) 不依赖于 max(visits.date)

另一种尝试是

SELECT users.id, users.name, t.date, locations.name as location_name
FROM users
LEFT JOIN (
  SELECT MAX(date) as date, user_id
  FROM visits
  GROUP BY user_id
) AS t ON users.id = t.user_id
LEFT JOIN visits on visits.date = t.date
LEFT JOIN locations on locations.id = visits.location_id
ORDER BY location_name

但是当一个用户在同一日期有两次访问时就会出现问题(我不在乎选择哪个地方但它必须只有一个)

虽然不需要日期，但结果应该看起来像这样。

id  name     date                  location_name
1   Alpha    2000-02-03T00:00:00Z  Delta
2   Bravo    2000-01-01T00:00:00Z  Echo
3   Charlie  (null)                (null)

解决方案最好是在 ActiveRecord 中，但普通的 sql 也可以

http://sqlfiddle.com/#!17/ba2a6/1

Answer 1

下面的查询将产生所需的结果。

我已经在 CTE 中填充了您的示例数据。

也不需要像max这样的group by和聚合函数。这里不需要。

      with users(id,name) as (      select *      from (        values        (1, 'Alpha'),        (2, 'Bravo'),        (3, 'Charlie')      ) t    ), locations(id,name) as (      select *      from(        values        (4, 'Delta'),        (5, 'Echo'),        (6, 'Foxtrott')      ) t    ), visits(id,user_id,location_id,date) as (      select *      from(      VALUES      (1, 1, 4, '2000-02-03 00:00:00'),      (2, 1, 5, '2000-02-02 00:00:00'),      (3, 1, 6, '2000-02-01 00:00:00'),      (4, 2, 6, '2000-01-01 00:00:00'),      (5, 2, 5, '2000-01-01 00:00:00')      ) t    ), res as (    select      distinct on(user_id)      u.id as user_id,      u.name,      l.name as location_name,      date    from visits v    join locations l on l.id=location_id    right join users u on u.id=v.user_id    order by user_id,date desc   )   select * from res order by location_name