Mysql(通用sql？): Sort three unrelated tables by the same field

Question

假设我有三个表:用户、测验和问题。每个都有一个 id(整数)字段和一个created_at(日期时间)字段。我希望能够将所有这些表放在一起，按created_at desc排序，然后获取记录的“页面”(即通过操作偏移量)，例如，页面是十条记录。即，我想从这三个表中的任何一个中获取最近创建的十条记录。

我的第一个想法就是进行外部联接，但这会导致一个巨大的联接表，这实际上不是很有效。这也是根本上错误的 - 我不想组合所有记录，我只是想有效地将​​它们全部放在一个列表中。

然后我尝试将两个表放入 from 子句(对于两个表只是为了测试它)并使用 comcat 对表进行排序:

select users.id, users.created_at, quizzes.id, quizzes.created_at, concat(users.created_at, quizzes.created_at) from users, quizzes order by concat(users.created_at, quizzes.created_at) desc limit 5;

但这也不起作用，因为这两个表再次组合在一起，并且需要很长时间(大约 10 秒)来执行查询。另外，我认为 concat 不是正确的方法。

有人能看到我正在尝试做什么并帮助我吗？我觉得我没有很好地解释它，也许是周一早上综合症。干杯，最大

编辑 - 这是我的解决方案，是下面托马拉克答案的调整版本。我使用的是updated_at字段而不是created_at，但这只是另一个日期时间字段。

SELECT id, updated_at FROM ( 
  SELECT concat('user_',id) as id, updated_at FROM users 
  UNION SELECT concat('quiz_',id) as id, updated_at FROM quizzes 
  UNION SELECT concat('question_',id) as id, updated_at FROM questions ) 
  as everything ORDER BY updated_at DESC LIMIT 0, 10;

我对 Tomalak 的回答遇到的一个问题是，您无法跟踪每条记录来自哪个表。因此，在上面，我将表名称连接到 id 的开头，因此最终会得到如下所示的内容:

+---------------+---------------------+
| id            | updated_at          |
+---------------+---------------------+
| user_1        | 2010-11-23 16:38:11 | 
| quiz_1306     | 2010-11-23 10:37:49 | 
| user_2        | 2010-11-22 11:57:08 | 
| user_401      | 2010-11-22 11:48:20 | 
| quiz_1303     | 2010-11-22 11:47:53 | 
| question_5163 | 2010-11-22 11:42:20 | 
| question_5162 | 2010-11-22 11:42:16 | 
| question_5161 | 2010-11-22 11:37:11 | 
| question_5160 | 2010-11-22 11:34:55 | 
| quiz_1304     | 2010-11-22 11:31:54 | 
+---------------+---------------------+
10 rows in set (0.16 sec)

Answer 1

像这样吗？

SELECT 
  tbl, id, updated_at 
FROM
  (
    SELECT 'users' AS tbl, id, updated_at FROM users
  UNION
    SELECT 'quizzes',      id, updated_at FROM quizzes
  UNION
    SELECT 'questions',    id, updated_at FROM questions
  ) as everything
ORDER BY
  updated_at DESC
LIMIT 10;

就查询效率而言，可能这个变体运行得更快:

SELECT 
  tbl, id, updated_at 
FROM
  (
    SELECT   'users' AS tbl, id, updated_at 
    FROM     users 
    ORDER BY updated_at DESC LIMIT 10
  UNION
    SELECT   'quizzes',      id, updated_at 
    FROM     quizzes
    ORDER BY updated_at DESC LIMIT 10
  UNION
    SELECT  'questions',     id, updated_at 
    FROM     questions 
    ORDER BY updated_at DESC LIMIT 10
  ) as everything
ORDER BY
  updated_at DESC
LIMIT 10;

您应该在每个单独的表上都有一个 (updated_at, id) 索引。

这样，查询就可以完全通过查看索引来完成，该索引已经排序并且还包含id字段。不必查看原始表，除非您想向输出添加更多字段。此外，“外部”ORDER BY(不能使用索引)只需对 30 行进行排序。