mysql - 选择最近一天的行

我正在尝试实现数据库中每个用户（用户名）最近一天（review_date）写的评论（标题）

My code is :

select tb1.*, tb2.* from
(select username, via_mobile as pc, max(review_date) as pcdate from tripadvisor where username != "" and via_mobile='false' group by username) tb1
join
(select username, via_mobile as mobile, max(review_date) as mobile from tripadvisor whereusername != "" and via_mobile='true' group by username) tb2
on tb1.username = tb2.username;

问题是我无法在正确的日期获得正确的审查。
例如 ：

username; review_date; title
Alan; 2012-12-18 ;"If..."

但它应该显示Alan; 2012-12-18; "Nice hotel"

您能帮我修复代码吗？

最佳答案

您的问题尚不清楚，但是如果我理解正确，您正在寻找具有最高日期的每个完整行，并按用户名区分/分组？这应该给你：

SELECT
  username,
  review_date,
  title
FROM
  tripadvisor AS ta
  INNER JOIN (
    SELECT
      username,
      max(review_date) AS review_date
    FROM
      tripadvisor
    GROUP BY
      username
  ) AS max_table
    ON ta.username = max_table.username
      AND ta.review_date = max_table.review_date;
WHERE
  username != ''
  -- any extra where clauses go here

参见：How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

关于mysql - 选择最近一天的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42403810/