MySql获取id = xxx的所有行，并且具有相同id的行中其他表中的最新列大于1 [英] MySql get all rows where id = xxx and where the newest column in other table in row with same id is greater than one

问题描述

我有两个 mysql 表:tbl_post 和 tbl_post_public

I have two mysql-tables: tbl_post and tbl_post_public

tbl_post 看起来像这样:

id | post_id   |  author  |  date (unix time)
--------------------------------
 0 |  xxxxx1   | User1   |  1489926071
 1 |  xxxxx2   | User2   |  1489926075
 2 |  xxxxx3   | User3   |  1489926079

此表包含用户发布的所有帖子

this table includes all posts by users

现在我有 tbl_post_public 表:此表包含信息，如果用户的帖子应该是公开的.如果用户从 public 更改为 private，则行不会更新.它只是添加一个具有相同 post_id 但具有更新时间戳的新行0 = 公开 |1 = 私人

now i have the tbl_post_public table: this table contains the information, if the post of the user should be public. if the user changes from public to private, the row dosent get updated. it just adds a new row with the same post_id but with a newer timestamp 0 = public | 1 = private

    id | post_id | public | date (unix time)
    -----------------------------------------
 --> 0 |  xxxxx1 |  0     |  1489926071 <--| this two rows have the same
     1 |  xxxxx2 |  1     |                | post_id but the second is
     2 |  xxxxx3 |  0     |                | public = 1. i need to get 
 --> 3 |  xxxxx1 |  1     |  1489926099 <--| the second row because its newer
                    ^
                    |

所以，在结果中我想要 10 行 (LIMIT 10) ORDERED BY tbl_post DESC WHERE author="User1" 中的日期列和 WHERE the newest(date-column in tbl_post_public) tbl_post_public 中的行(具有相同的 post_id)并且 public = 0

so, in the result i want to have 10 Rows (LIMIT 10) ORDERED BY the date-column in tbl_post DESC WHERE author="User1" and WHERE the newest(date-column in tbl_post_public) row in tbl_post_public (wich has the same post_id) and has public = 0

我希望你理解我的问题 &抱歉我的英语不好:)

i hope you understand my question & sorry for my bad english :)

推荐答案

您可以通过多种方式获取公共表中的最新行.如果您要过滤帖子，我会推荐相关子查询:

You can get the most recent row in the public table in various ways. If you are filtering the posts, I would recommend a correlated subquery:

select p.*
from (select p.*,
             (select pp.public
              from tbl_post_public pp
              where pp.post_id = p.post_id
              order by date desc
              limit 1
             ) as latest_public
      from tbl_post p
      where . . .
     ) p
where public = 0
order by date desc
limit 10;

出于性能考虑，您需要在 tbl_post_public(post_id, date) 上建立索引.

For performance purposes, you will want an index on tbl_post_public(post_id, date).

如果您没有 where 子句并且在 tbl_post(date) 上有索引，那么这可能会快一点:

If you don't have a where clause and you have an index on tbl_post(date), then this is probably a bit faster:

select p.*
from (select p.*,
             (select pp.public
              from tbl_post_public pp
              where pp.post_id = p.post_id
              order by date desc
              limit 1
             ) as latest_public
      from tbl_post p
      order by p.date desc
     ) p
where public = 0
limit 10;

这篇关于MySql获取id = xxx的所有行，并且具有相同id的行中其他表中的最新列大于1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！