选择最近一天的行

I am trying to achieve the comment (title) written on the most recent day (review_date) from each user (username) in the database

我试图从数据库中的每个用户(用户名)获得最近一天(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;

The problem is I cannot get the right review for the right date. For example :

问题是我无法在正确的日期获得正确的评论。例如 :

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

But it should display Alan; 2012-12-18; "Nice hotel"

但它应该显示艾伦; 2012年12月18日; “不错的酒店”

Can you help me to fix the code.

你能帮我修改代码吗?

3 个解决方案

#1

Your question is a little unclear, but if I understand correctly, you're looking for each full row with the highest date, selected distinctly/grouped by username? This should give you that:

您的问题有点不清楚,但如果我理解正确,您正在查找具有最高日期的每个完整行,选择明确/按用户名分组?这应该给你:

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

See: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

请参阅:如何通过SQL中的另一列选择MAX(列值),DISTINCT的行?

#2

If the goal is to return the most recent review title for "mobile" and for "pc", I'd do something like this:

如果目标是返回“mobile”和“pc”的最新评论标题,我会做这样的事情:

SELECT q.username
     , MAX(q.pc_date)     AS pc_date
     , MAX(p.title)       AS pc_title
     , MAX(q.mobile_date) AS mobile_date
     , MAX(r.title)       AS mobile_title
  FROM ( SELECT t.username
              , MAX(IF(t.via_mobile='false',t.review_date,NULL) AS pc_date
              , MAX(IF(t.via_mobile='true',t.review_date,NULL) AS mobile_date
           FROM tripadvisor t
          WHERE t.username <> ''
            AND t.via_mobile IN ('true','false')
          GROUP
             BY t.username
       ) q
  LEFT
  JOIN tripadvisor p
    ON p.username    = q.username
   AND p.review_date = q.pc_date
   AND p.via_mobile  = 'false'
  LEFT
  JOIN tripadvisor r
    ON r.username    = q.username
   AND r.review_date = q.mobile_date
   AND r.via_mobile  = 'true'
 GROUP
    BY q.username

If the user has only "mobile" reviews and no "pc" reviews, this query will return a row, but with NULL values for the "pc" columns. Similarly, the query will return NULL values for the "mobile" columns for a user that has only "pc" reviews.

如果用户只有“移动”评论而没有“pc”评论,则此查询将返回一行,但“pc”列的值为NULL。类似地,对于仅具有“pc”评论的用户,查询将为“移动”列返回NULL值。

The query could easily be changed to only returns rows for users that have both "mobile" and "pc" reviews, to be closer to the original using the INNER JOIN.

查询可以很容易地更改为仅返回具有“移动”和“pc”评论的用户的行,使用INNER JOIN更接近原始行。

If the goal is simpler, just to return just the most recent review...

如果目标更简单,只需返回最近的评论......

SELECT r.username
     , r.review_date
     , MAX(r.title)      AS title
     , MAX(r.via_mobile) AS via_mobile
  FROM ( SELECT t.username
              , MAX(t.review_date) AS max_review_date
           FROM tripadvisor t
          WHERE t.username <> ''
            AND t.via_mobile IN ('true','false')
          GROUP
             BY t.username
       ) q
  JOIN tripadvisor r
    ON r.username    = q.username
   AND r.review_date = q.max_review_date
   AND r.via_mobile IN ('true','false')
 GROUP
    BY r.username
     , r.review_date

The results of this query are somewhat indeterminate when a username has multiple rows with identical (most recent) review_date. This guarantees a single row will be returned, but the title and via_mobile may not be from the same row.

当用户名具有相同(最近)review_date的多个行时,此查询的结果有些不确定。这保证将返回单行,但title和via_mobile可能不是来自同一行。

#3

Your question could be expressed as an existence test: show the rows for which the review date matches the latest review date for that user.

您的问题可以表示为存在测试:显示审阅日期与该用户的最新审核日期匹配的行。

Existence is tested with EXISTS in a correlated subquery:

在相关子查询中使用EXISTS测试存在性:

SELECT * FROM tripadvisor as T
where exists ( 
    select 1 from tripadvisor
    where username = T.username
    group by username
    having MAX(review_date) = T.review_date
);

#1