复杂的SQL(也许是外部联接) [英] Complex SQL (Maybe Outer Joins)

问题描述

我有2个表，如下所示.

I have 2 tables as defined below.

评论表
id
user_id
评论
标题
日期时间

Reviews table
id
user_id
review
title
datetime

PeopleFollows表
id
user_id
following_user_id
日期时间

PeopleFollows table
id
user_id
following_user_id
datetime

我想运行1个查询以按日期时间获取前10个结果.例如，如果我有以下数据:

I want to run 1 query to get the top 10 results by datetime. So for instance, if I have the following data:

查看表

1 user1  "This is my review" "title" 2011-01-10  
2 user1 "Another review" "title again" 2011-01-08  

人们关注表格

1 user2 user1 2011-01-09 

我想要这样的结果:

Review id=2  
People follows id=1
Review id = 1  

我认为可以做到这一点的唯一方法是对X个限制进行单独的查询，然后将它们组合以获得X个结果.

The only way I think I can do this is to do separate queries of X limit, then combine them to get X results.

我可能需要解释更多.

推荐答案

如果您按日期时间对两个表进行索引，并且按日期时间在UNION之前对每个查询进行排序，则不需要全面扫描.

You don't need full scans if you index both of the tables by datetime and also order each query by datetime before UNION.

(SELECT "Review", id, datetime FROM Reviews ORDER BY datetime DESC LIMIT 10)
UNION 
(SELECT "People", id, datetime FROM PeopleFollows ORDER BY datetime DESC LIMIT 10)
ORDER BY datetime DESC
LIMIT 10

这篇关于复杂的SQL(也许是外部联接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！