连接表需要在分组之前加入表的问题 [英] Problem joining tables where joined table needs to be ordered before grouping

问题描述

我有一个场景，这在纸面上看似简单，但我在实践中无法按照需要进行工作。

I have a scenario, which is seemingly simple on paper, but I'm having trouble getting to work as desired in practice.

我有两张桌子相关专栏）：

I have two tables (only relevant columns presented):

| Thread
+----------
| ThreadID 

| Post
+----------
| PostID
| ThreadID
| Posted (Datetime)

现在，我想要做的是加入Thread和Post，按线程ID。但我想通过Post.Posted以降序排列。用简单的英语，我想加入与最近发布的帖子相关的主题。

Now, what I want to do, is to join Thread and Post, grouping by ThreadID. But I want to order by Post.Posted in descending order. In plain english, I want to join Thread on the most recent Post relating to it.

到目前为止我的SQL：

My SQL so far:

SELECT Thread.ThreadID, Post.PostID, Post.Created
FROM Thread
LEFT JOIN Post ON Post.ThreadID = Thread.ThreadID
GROUP BY Thread.ThreadID
ORDER BY Post.Created DESC 

它完成这项工作，但订单的顺序是不正确的，因为订单当然只能在分组完成后应用。

It does the job, but the order of the join is not correct, as the ordering is, of course, applied only after the grouping is performed.

当然，必须有一种方法来完成我需要？

Surely, there must be a way to accomplish what I need?

推荐答案

select t.ThreadID, p.PostID, p.Posted
from Thread t
inner join (
    select ThreadID, Max(Posted) as MaxPosted
    from Post
    group by ThreadID       
) pm on t. ThreadID = pm.ThreadID
inner join Post p on pm.ThreadID = p.ThreadID and pm.MaxPosted = p.Posted
order by p.Posted desc

这篇关于连接表需要在分组之前加入表的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！