MySQL查询帮助(半唯一结果)

Question

user_id | user_destination | user_date_out | user_date_in | user_purpose | uid
0095    | NYC              | 2010-11-25    | 2010-11-26   | Work         | 1
0105    | Seattle          | 2010-11-15    | 2010-11-20   | Work         | 2
0095    | Home             | 2010-11-10    | 2010-11-11   | Personal     | 3
0123    | Nashville        | 2010-11-10    | 2010-11-12   | Doctober     | 4

我在一个MySQL表中有上面的数据。我需要一个查询，如果该行是该user_id的最新user_date_out，它将输出该行。很难解释，但是应该通过查询显示的行是uid 2、3和4(对于同一用户，UID 1会丢失，因为user_date_out比UID 3“旧”)。有人能帮我吗？提前感谢！

编辑:我用下面的查询解决了这个问题：

SELECT *
FROM `table`
WHERE `uid` = (
    SELECT `uid`
    FROM `table` as `alt`
    WHERE `alt`.`user_id` = `table`.`user_id`
    ORDER BY `user_date_out`
    LIMIT 1
)
ORDER BY `user_date_out`

Answer 1

每个请求

SELECT *
FROM `table`
WHERE `uid` = (
    SELECT `uid`
    FROM `table` as `alt`
    WHERE `alt`.`user_id` = `table`.`user_id`
    ORDER BY `user_date_out`
    LIMIT 1
)
ORDER BY `user_date_out`

Answer 2

SELECT t.*, a_subquery.min_user_date_out
  FROM your_table t
       JOIN (SELECT user_id, MIN(user_date_out) AS min_user_date_out 
               FROM your_table
           GROUP BY user_id) a_subquery
       ON a_subquery.min_user_date_out = t.user_date_out 
      AND a_subquery.user_id = t.user_id

由于MySQL有时会遇到使用子查询的问题，因此下面是使用自排除连接的相同等价查询：

SELECT t.*
  FROM your_table t
       LEFT JOIN your_table t2 
       ON t.user_id = t2.user_id
       AND t.user_date_out > t2.user_date_out
 WHERE t2.user_id IS NULL

请注意，如果给定的user_date_out具有多个user_id，则这两个查询都不起作用。