mysql - mysql - 根据表中列值最高的 id 连接表行 - 多个连接和条件

Question

在询问之前我已经搜索过 SO 并尝试了我发现的东西 - 由于多个连接和条件，我参与的更多，并且无法获得正确的结果。

在这里输入基本数据的SQL Fiddle 。

下面的查询没有给出我想要的结果，但给出了我想要实现的目标。我想为每个 computer_id 返回 1 个结果，其中 time.capture_timestamp 介于特定的开始/结束值之间，并且是表中该计算机 ID 的最大值，包括该行的其他列值。我已经尝试了一些我在这里找到的涉及 MAX() 和子查询的不同的东西，但似乎无法得到我想要的东西。

SELECT
  computers.computer_name,
  users.username,
  time.title,
  time.capture_timestamp
FROM computers
INNER JOIN time
  ON time.computer_id = computers.computer_id AND time.capture_timestamp >= 0 AND time.capture_timestamp <= 9999999999
INNER JOIN users
  ON users.user_id = time.user_id
GROUP BY computers.computer_id
ORDER BY time.capture_timestamp DESC

小提琴原样将返回：

computer_name   username    title           capture_timestamp
computer1       user1       some title      1595524341
computer2       user3       some title3     1595524331

而我想要的结果实际上是：

computer_name   username    title           capture_timestamp
computer1       user2       some title2     1595524351
computer2       user3       some title3     1595524331

...基于小提琴中的示例值。是的，在此示例中，开始/结束时间值包括“所有内容”，但在使用中实际上会提供时间戳范围。

标签： mysqlgreatest-n-per-group

Answer 1

使用ROW_NUMBER：

WITH cte AS (
    SELECT c.computer_name, u.username, t.title, t.capture_timestamp,
           ROW_NUMBER() OVER (PARTITION BY c.computer_id
                              ORDER BY t.capture_timestamp DESC) rn
    FROM computers c
    INNER JOIN time t ON t.computer_id = c.computer_id
    INNER JOIN users u ON u.user_id = t.user_id
    WHERE t.capture_timestamp BETWEEN 0 AND 9999999999
)

SELECT computer_name, username, title, capture_timestamp
FROM cte
WHERE rn = 1;