I've looked at some other questions here but can't find a solution that matches mine. I don't really understand some of the parts so I can't adapt their solutions to my problem.
我在这里看了一些其他问题，但找不到与我匹配的解决方案。我真的不了解其中的一些部分，所以我不能让他们的解决方案适应我的问题。
I have a table like this:
我有这样一张桌子：
post_id, post_author, post_message, post_timestamp, post_thread and.. thread_id, thread_author, thread_message, thread_timestamp and I'd like to fetch * from both (different) tables, and order by their timestamp so I could fetch the latest from both.
post_id，post_author，post_message，post_timestamp，post_thread和.. thread_id，thread_author，thread_message，thread_timestamp，我想从两个（不同的）表中获取*，并按时间戳排序，以便我可以从两者中获取最新信息。
How can I achieve this? As I said I looked into some other solutions here but can't adapt it as the ones I can find, have the same name on their timestamp field.
我怎样才能做到这一点？正如我所说，我在这里研究了其他一些解决方案，但无法将其作为我能找到的解决方案，在其时间戳字段上具有相同的名称。
Thanks in advance.
提前致谢。
1 个解决方案
#1
1
You're going to want to use a LEFT JOIN from thread to post, and you can sort using a coalesce. The performance will probably be horrible, but let's get something working first and then tune it.
您将要使用从线程发布的LEFT JOIN，并且您可以使用coalesce进行排序。性能可能会很糟糕，但让我们先做一些工作然后调整它。
SELECT p.*, t.*, coalesce(p.post_timestamp, t.thread_timestamp) as timestamp FROM thread t
LEFT JOIN (SELECT * FROM post ORDER BY post_timestamp DESC LIMIT 1) as p on p.post_thread = t.thread_id
ORDER BY timestamp DESC
LIMIT 10
;
The coalesce function takes a list of arguments and returns the first one that's not null. So in this case, it will return the post timestamp, unless there is no post, in which case it will return the thread timestamp. The join is a LEFT join so that even if the subselect returns zero results, there will still be a result row.
coalesce函数接受一个参数列表并返回第一个不为null的参数。所以在这种情况下，它将返回post poststamp，除非没有post，在这种情况下它将返回线程时间戳。连接是LEFT连接，因此即使subselect返回零结果，仍会有结果行。
#1
1
You're going to want to use a LEFT JOIN from thread to post, and you can sort using a coalesce. The performance will probably be horrible, but let's get something working first and then tune it.
您将要使用从线程发布的LEFT JOIN，并且您可以使用coalesce进行排序。性能可能会很糟糕，但让我们先做一些工作然后调整它。
SELECT p.*, t.*, coalesce(p.post_timestamp, t.thread_timestamp) as timestamp FROM thread t
LEFT JOIN (SELECT * FROM post ORDER BY post_timestamp DESC LIMIT 1) as p on p.post_thread = t.thread_id
ORDER BY timestamp DESC
LIMIT 10
;
The coalesce function takes a list of arguments and returns the first one that's not null. So in this case, it will return the post timestamp, unless there is no post, in which case it will return the thread timestamp. The join is a LEFT join so that even if the subselect returns zero results, there will still be a result row.
coalesce函数接受一个参数列表并返回第一个不为null的参数。所以在这种情况下，它将返回post poststamp，除非没有post，在这种情况下它将返回线程时间戳。连接是LEFT连接，因此即使subselect返回零结果，仍会有结果行。