您将需要两个连接。类似下面的内容应该可以帮助您开始(尽管我并不 100% 理解之间的关系)pm_data
and pm_info
):
SELECT pm_info.is_read, sender.usrFirst + ' ' + sender.usrLast as sender_name,
pm_data.date_sent, pm_data.title, pm_data.thread_id
FROM pm_info
INNER JOIN pm_data ON pm_info.thread_id = pm_data.thread_id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = tblUsers.usrID
WHERE pm_info.receiver_id = @USER_ID /*in this case, 2*/
ORDER BY pm_data.date_sent DESC
我假设之间的关系pm_data
and pm_info
是线程ID。如果不是,您应该能够根据需要调整上述内容。我还按发送日期排序,但是它不会将线程保持在一起。从您提出问题的方式来看,我不确定您是否想将它们放在一起。
如果你想将线程保持在一起,您将需要一个更复杂的查询:
SELECT pm_info.is_read, sender.usrFirst + ' ' + sender.usrLast as sender_name,
pm_data.date_sent, pm_data.title, pm_data.thread_id
FROM pm_info
INNER JOIN pm_data ON pm_info.thread_id = pm_data.thread_id
INNER JOIN tblUsers AS sender ON pm_data.sender_id = tblUsers.usrID
INNER JOIN (SELECT thread_id, MAX(date_sent) AS max_date
FROM pm_data
GROUP BY thread_id) AS most_recent_date
ON pm_data.thread_id = most_recent_date.thread_id
WHERE pm_info.receiver_id = @USER_ID /*in this case, 2*/
ORDER BY most_recent_date.max_date DESC, pm_data.thread_id,
pm_data.date_sent DESC
此查询使用子选择来查找每个线程的最近修改日期,然后首先按此排序。