Sql连接按两个日期排序的两个表
Sql连接按两个日期排序的两个表
提问于 2016-09-15 23:06:26
我有两张桌子
主表
Id_post
Id_user_post
Post
Date_post辅助表
Id_mentioned
Id_user
Id_user_post
Id_post
Date_mentioned例如，第一个表中有13条记录，第二个表中有3条记录。
第一个表记录(13)
1 herman this is text 1 10:00:00 15/09/2016
2 jhon this is text 2 11:00:00 15/09/2016
3 carl this is text 3 12:00:00 15/09/2016
4 herman this is text 4 13:00:00 15/09/2016
5 herman this is text 5 14:00:00 15/09/2016
6 herman this is text 6 15:00:00 15/09/2016
7 jhon this is text 7 16:00:00 15/09/2016
8 herman this is text 8 17:00:00 15/09/2016
9 herman this is text 9 18:00:00 15/09/2016
10 carl this is text 10 19:00:00 15/09/2016
11 herman this is text 11 20:00:00 15/09/2016
12 carl this is text 12 21:00:00 15/09/2016
13 herman this is text 13 22:00:00 15/09/2016第二个表记录
1 herman jhon 7 11:20:00 15/09/2016
2 jhon carl 10 12:30:00 15/09/2016
3 herman carl 3 14:50:00 15/09/2016如果我选择hermann的帖子，我想要下一个结果，按日期排序(发布日期和提到的日期)
1 herman this is text 1 10:00:00 15/09/2016
7 jhon this is text 7 11:20:00 15/09/20167 (date mentioned)
4 herman this is text 4 13:00:00 15/09/2016
5 herman this is text 5 14:00:00 15/09/2016
3 carl this is text 3 14:50:00 15/09/2016 (date mentioned)
6 herman this is text 6 15:00:00 15/09/2016
8 herman this is text 8 17:00:00 15/09/2016
9 herman this is text 9 18:00:00 15/09/2016
11 herman this is text 11 20:00:00 15/09/2016
13 herman this is text 13 22:00:00 15/09/2016在这些结果中出现了hermman提交的帖子和第二个表中提到的，由date_mentioned排序(它的名称类似于twitter，在主要结果中显示自己的帖子和不属于同一所有者的retwitts )
我尝试了sql join left
Select * from $table_posts left join $table_mentions on $table_posts.id_user=$table_mentions.id_user order by date_post,date_mentioned我也试过了，但一无所获。
SELECT * FROM $table_posts WHERE id_user_post=(SELECT id_user_post FROM $table_mentions WHERE id_user_post='$id_user') AND id_user_post='$id_user' ORDER BY date_post,date_mentioned DESC Stack Overflow用户
发布于 2016-09-16 00:14:34
首先，如果我正确理解了你的设计，你需要规范化你的数据库。如我所见，Id_post指的是第一个表中的id。但是，您也有指向Id_post用户的User_post，这是不必要的。现在，您所描述的不是确切的连接，而是第一个表和第一个表和第二个表的连接的并集。类似于：
SELECT t.Id_Post, t.Id_User, t.Post, t.Date_post FROM (
SELECT a.Id_Post, a.Id_User, a.Post, a.Date_post FROM table_posts a
UNION ALL
SELECT b.Id_Post, b.Id_User, b.Post, c.Date_mentioned as Date_post
FROM table_posts b JOIN table_mentions c ON b.Id_post = c.Id_Post
) t ORDER BY t.Date_post DESC;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39514492
复制相关文章
相似问题
腾讯云开发者
