MySQL从多个表中选择+来自其他地方的最后一行

Question

你好，我有这 4 个表的结构

Table: Users [ id, username, password, bouquet_id ]
Table: Bouquets [ id, bouquet_name, stream_ids = serialized array ]
Table: Streams [ id, channel_name ]
Table: activity [ id, user_id, stream_id ]

我想选择所有用户，但也包含其他表中的信息 + 每个用户的表事件中的最后一行

例如以下查询:

SELECT t1.*,t2.`bouquet_name`
FROM `users` t1,`bouquets` t2
WHERE t1.`bouquet_id ` = t2.`id`
ORDER BY t1.id DESC

从前 2 个表中获取数据，并将 bouquet_id 分配给它的花束名称。现在我想查询事件表中的最后一行及其流名称[基于stream_id]

以下查询完成了我想要的工作[每个用户]

SELECT t1.channel_name
FROM `streams` t1,`activity` t2
WHERE t2.user_id = **'%d'** AND t1.id = t2.stream_id
ORDER BY t2.id DESC
LIMIT 1

但是它有点慢，因为对于表“users”中的每个用户，我运行 2 个查询。我希望将上面的 2 个查询作为一个嵌入在一起，以便我能够从前两个表中选择数据，但根据 user_id 从表事件中选择最后一行。

希望你能理解我谢谢

Answer 1

您不应该依赖于“last row”=“具有最大id的行”。由于 MySQL 提供了设置主主复制的可能性，从而提供了以或多或少任意顺序分配 auto_increment 值的可能性，因此这种假设并不总是正确的。额外的 timestamp 列会更好。

您可以选择每个用户最近的事件，例如:

SELECT user_id, MAX(id) FROM activity GROUP BY user_id

然后，您必须将其加入到您的查询中，并加入到基本 activity 表中以检索您真正想要的数据。您可能还想将联接替换为左联接，因此无论 bouquets 或 activity 中是否存在行，您都将始终检索所有用户:

SELECT t1.*, t2.`bouquet_name`, activity.*
FROM `users` t1
LEFT JOIN `bouquets` t2
 ON t1.`bouquet_id ` = t2.`id`
LEFT JOIN (
  SELECT user_id, MAX(id) AS maxid FROM activity GROUP BY user_id
) mostrecent
 ON t1.user_id = mostrecent.user_id
LEFT JOIN activity
 ON t1.user_id = activity.user_id AND activity.id = mostrecent.maxid
ORDER BY t1.id DESC

然后你也可以加入流数据

SELECT t1.*, t2.`bouquet_name`, activity.stream_id, streams.channel_name
FROM `users` t1
LEFT JOIN `bouquets` t2
 ON t1.`bouquet_id ` = t2.`id`
LEFT JOIN (
  SELECT user_id, MAX(id) AS maxid FROM activity GROUP BY user_id
) mostrecent
 ON t1.user_id = mostrecent.user_id
LEFT JOIN activity
 ON t1.user_id = activity.user_id AND activity.id = mostrecent.maxid
LEFT JOIN streams
 ON activity.stream_id = streams.id
ORDER BY t1.id DESC