Mysql为每个用户选择最旧的记录 [英] Mysql select oldest record for each user

问题描述

我正在尝试在 PHP 中为数据库中的每个用户创建一个包含最旧条目的列表.

I'm trying to create a list in PHP of the oldest entries for each user in the database.

SELECT *, 
MIN(`entries`.`entry_date`) 
AS entry_date 
FROM (`entries`) 
JOIN `user_profiles` 
ON `user_profiles`.`user_id` = `entries`.`user_id` 
WHERE `status` = 1 
GROUP BY `entries`.`user_id`

我正在使用查询从 entries 表中检索最旧的日期条目，使用 MIN() 并加入表 user_profiles其他数据.查询应为每个用户选择最旧的条目.它似乎可以工作，但是当我回显它们时，它在某些条目上检索了错误的 entry_date 字段.请帮忙，我无法发现我做错了什么..

I'm using the query to retrieve from the entries table the oldest dated entry using MIN()and joining with table user_profiles for other data. The query should select the oldest entry for each user. It seems to work but it retrieves the wrong entry_date field on some entries when I echo them. Please help, I can't spot what I'm doing wrong..

推荐答案

您需要使用子查询来获取每个用户的 (user_id, entry_date) 对，然后将其加入一个查询选择感兴趣的记录:

You need to use a subquery to obtain the (user_id, entry_date) pairs for each user, then join that with a query that selects the records of interest:

SELECT *
FROM   entries
  NATURAL JOIN (
    SELECT   user_id, MIN(entry_date) AS entry_date
    FROM     entries
    GROUP BY user_id
  ) AS tmin
  JOIN user_profiles USING (user_id)
WHERE  status = 1

这篇关于Mysql为每个用户选择最旧的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！