当前位置：文江博客话题详情

如何从多个表中获取按时间排序的最后 30 个项目？

发布于 2024-11-08 07:16:16 字数 194 浏览 0 评论 0原文

我有 3 个表可以从中获取信息：

文章
发布
图片

所有这些表都有“created_at”列。

我想获取按created_at列排序的最后30个项目抛出sql查询，在最坏的情况下，我将不得不从任何表中弹出 30 个项目，并通过 php 代码从数组中对其进行排序。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初吻给了烟 2024-11-15 07:16:16

像这样的事情怎么样：

(SELECT * FROM articles)
UNION 
(SELECT * FROM posts)
UNION 
(SELECT * FROM pictures)
ORDER BY created_at DESC 
LIMIT 30

这尚未经过测试，但应该可以工作。确保将“*”更改为您正在使用的任何字段。

What about something like this:

(SELECT * FROM articles)
UNION 
(SELECT * FROM posts)
UNION 
(SELECT * FROM pictures)
ORDER BY created_at DESC 
LIMIT 30

This is untested, but should work. Make sure to change "*" to whatever fields you are using.

回复收藏 0 原文

抱猫软卧 2024-11-15 07:16:16

基于过去的答案，并在此处添加一点优化：

SELECT created_at, tbl, id, name, author_id FROM
  (SELECT created_at, "articles" as tbl, id, name, author_id 
   FROM `articles` ORDER BY created_at DESC LIMIT 30
   UNION 
  SELECT created_at, "posts" as tbl, id, name, author_id 
   FROM `posts` ORDER BY created_at DESC LIMIT 30
   UNION 
  SELECT created_at, "pictures" as tbl, id, name, author_id 
   FROM `pictures` ORDER BY created_at DESC LIMIT 30
  )
ORDER BY created_at DESC LIMIT 30;

FWIW - 这是未经测试的，可能包含愚蠢的错误。

我首先假设created_at 在所有三个表上都建立了索引。
我还将限制每个表仅返回“最后 30 个项目”，这样我们就不会在可能巨大的派生表（我们无法在其中建立索引）上进行排序。

因此，派生表上的最终“排序依据”保证排序 <= 90 行。
比订购 3 个表的总并集要好得多。

Building on the past answers, and add a little bit of optimization here:

SELECT created_at, tbl, id, name, author_id FROM
  (SELECT created_at, "articles" as tbl, id, name, author_id 
   FROM `articles` ORDER BY created_at DESC LIMIT 30
   UNION 
  SELECT created_at, "posts" as tbl, id, name, author_id 
   FROM `posts` ORDER BY created_at DESC LIMIT 30
   UNION 
  SELECT created_at, "pictures" as tbl, id, name, author_id 
   FROM `pictures` ORDER BY created_at DESC LIMIT 30
  )
ORDER BY created_at DESC LIMIT 30;

FWIW - this is untested and may contain stupid errors.

I'm first going to assume that created_at is indexed on all three tables.
I'm also going to restrict each table to only returning the 'last 30 items' so we're not ordering on a potentially huge derived table (in which we can't index).

So, the final 'order by' on the derived table is guaranteed to order <= 90 rows.
Much better than ordering the total union of 3 tables.

回复收藏 0 原文

转角预定愛 2024-11-15 07:16:16

类似的东西：

SELECT * FROM
  (SELECT * FROM articles UNION SELECT * FROM posts UNION SELECT * FROM pictures)
ORDER BY created_at DESC LIMIT 30

几乎可以肯定，您需要将 * 替换为您要选择的两个表共有的字段列表（例如 ID），也许还需要用一些东西来识别它是什么类型的对象（例如，SELECT a, b, 'article' AS item_type）

请小心放置 WHERE 子句的位置。理想情况下，您可以在每个内部选择上放置一个以限制其中包含的对象，而不是在外部放置一个必须等待整个 UNION 操作的大量 WHERE要首先执行。

Something like:

SELECT * FROM
  (SELECT * FROM articles UNION SELECT * FROM posts UNION SELECT * FROM pictures)
ORDER BY created_at DESC LIMIT 30

Almost certainly you will need to replace * with the list of fields common to both tables that you want to select (e.g., ID), and perhaps something to identify what type of object it was (e.g., SELECT a, b, 'article' AS item_type)

Be careful where you put your WHERE clauses. Ideally you can put one on each of the inner selects to limit what objects are included there, rather than putting one massive WHERE on the outside that must wait for the entire UNION operation to be performed first.

回复收藏 0 原文

沦落红尘 2024-11-15 07:16:16

我会选择：

Select top 30 * from
(select acticles.created_at, ID 
from articles 
UNION
select posts.created_at, ID 
from posts 
UNION
select pictures.created_at, ID 
from pictures
ORDER BY 1)

I would go with:

Select top 30 * from
(select acticles.created_at, ID 
from articles 
UNION
select posts.created_at, ID 
from posts 
UNION
select pictures.created_at, ID 
from pictures
ORDER BY 1)

回复收藏 0 原文

~没有更多了~