在mysql中合并数据bt表

发布于 2024-08-01 22:03:38 字数 325 浏览 5 评论 0原文

我有三个具有以下结构的表：

tb1: id(AI, PK), uid, date, text
tb2: id(AI, PK), uid, date, text ... and so on

由于每个表都有额外的独特数据，我必须将它们分开。我想执行一个查询，该查询将合并并获取最后 20 个条目（即 date DESC）（具体来说，我需要 uid 和 text< /代码>) 来自所有 3 张桌子的总和（就好像这是一张大桌子一样）。

我该怎么做呢？

原文

I have three tables with the following structure:

tb1: id(AI, PK), uid, date, text
tb2: id(AI, PK), uid, date, text ... and so on

I have to keep them separate because of additional unqiue data that each table has.
I'd like to execute a query that will merge and get me the last 20 entries (ie, date DESC) (specifically, i need uid and text)
from all 3 tables combined (as if this was one big table).

How can I go about doing this?

分享到QQ

分享到微博

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

发布评论

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

日久见人心 2024-08-08 22:03:38

SELECT  *
FROM    (
        SELECT  uid, text
        FROM    tb1
        ORDER BY
                date DESC
        LIMIT 20
        ) q
UNION ALL
SELECT  *
FROM    (
        SELECT  uid, text
        FROM    tb2
        ORDER BY
                date DESC
        LIMIT 20
        ) q2
UNION ALL
        (
        SELECT  uid, text
        FROM    tb3
        ORDER BY
                date DESC
        LIMIT 20
        ) q3
ORDER BY
        date DESC
LIMIT 20

这比单纯的 UNION ALL 更有效。

请参阅我博客中的这篇文章进行性能比较：

MySQL：LIMIT on LIMIT

SELECT  *
FROM    (
        SELECT  uid, text
        FROM    tb1
        ORDER BY
                date DESC
        LIMIT 20
        ) q
UNION ALL
SELECT  *
FROM    (
        SELECT  uid, text
        FROM    tb2
        ORDER BY
                date DESC
        LIMIT 20
        ) q2
UNION ALL
        (
        SELECT  uid, text
        FROM    tb3
        ORDER BY
                date DESC
        LIMIT 20
        ) q3
ORDER BY
        date DESC
LIMIT 20

This is more efficient than a mere UNION ALL.

See this article in my blog for performance comparison: