从两个表中选择单独的行，按日期排序 [英] Select separate rows from two tables, order by date

问题描述

我不想在这里想要任何JOIN.我正在使用PHP构建包含两个表的RSS提要，我想从两个表中选择所有行，保持行分开，但按常见的created列进行排序.

I don't want to any kind of JOIN here. I'm building an RSS feed of two tables using PHP, and I want to select all the rows from the two tables, keeping the rows separate but sorting by a common created column.

例如，如果我有一个表foo:

For example, if I have a table foo:

id      downloads   views   created
-----------------------------------------------
1       12          23      2011-07-22 00:10:16
2       51          900     2011-07-22 10:11:45
3       8           80      2011-07-23 04:12:18

和一个表bar:

id      title       body    created
-----------------------------------------------
1       foo         ogblog  2011-07-21 10:54:07
3       bar         zip     2011-07-24 10:54:07
4       zip         bar     2011-07-25 10:54:07

我想从公共created列排序的两个表中选择所有数据，因此示例结果集为(由于不需要而忽略bar.id ):

I want to select all data from both tables ordered by the common created column, so an example resultset would be (ignoring bar.id as it's not needed):

id      title       body        downloads   views   created             | table
-------------------------------------------------------------------------------
NULL    bar         zip         NULL        NULL    2011-07-24 10:54:07 | bar
NULL    foo         ogblog      NULL        NULL    2011-07-21 10:54:07 | bar
1       NULL        NULL        12          23      2011-07-22 00:10:16 | foo
2       NULL        NULL        51          900     2011-07-22 10:11:45 | foo
3       NULL        NULL        8           80      2011-07-23 04:12:18 | foo
NULL    zip         bar         NULL        NULL    2011-07-25 10:54:07 | bar

不需要table列；我添加了它是为了使事情更容易理解.

The table column isn't needed; I added it to make things a little easier to understand.

希望我要做的事很明显；我想执行的操作不是从两个表的列中生成一行的JOIN，而是要获取具有公共列布局的所有行数据，其中表中不存在的任何列都放入了NULL.

Hopefully it's obvious what I want to do; instead of doing a JOIN where a row is generated from columns from two tables, I want to get all the row data with a common column layout where any column that doesn't exist in a table has NULL put into it.

如果需要澄清，请告诉我.

Please let me know if you need clarification.

推荐答案

使用伪列说明不同的结构，并用一个联合来连接它们，并使用父级选择来处理顺序:

Using dummy columns to account for the different structures, a union to join them and a parent select to handle the ordering:

SELECT * FROM (
    (SELECT foo.id, NULL AS title, NULL AS body, foo.downloads, foo.views, foo.created FROM foo)
    UNION ALL
    (SELECT NULL AS id, bar.title, bar.body, NULL AS downloads, NULL AS views, bar.created FROM bar)
) results
ORDER BY created ASC

这篇关于从两个表中选择单独的行，按日期排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！