代码之家 › 专栏 › 技术社区 › Trevor

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

mysql sql

Trevor · 技术社区 · 15 年前

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表中有上述数据。我需要一个查询,如果该行是该用户id的最新用户外出日期,则该查询将输出该行。很难解释,但应该通过该查询显示的行是uid 2、3和4(uid 1将丢失,因为该用户外出日期比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`

2 回复 | 直到 15 年前

krx 15 年前

按请求

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`

mechanical_meat nazca 15 年前

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 .