MySQL查询：选择每个日期的每个用户的最新记录。

Question

我正在处理大量数据。我想选择每个日期的最新记录中的每个用户。 我的表格如下：

 +---+--------+-----+--------+------+-----+
 |id |user_id |name |date    |time1 |time2|
 +---+--------+-----+--------+------+-----+
 |1  |1       |x    |12-1-15 |10:30 |21:30|
 |2  |1       |x    |12-1-15 |10:30 |21:30|
 |3  |2       |y    |12-1-15 |10:30 |22:30|
 |4  |1       |x    |13-1-15 |10:30 |18:30|
 |5  |2       |y    |13-1-15 |10:30 |18:30|
 |6  |2       |y    |13-1-15 |10:30 |20:30|
 +---+--------+-----+--------+------+-----+

我的输出将是：

 +---+--------+-----+--------+------+-----+
 |id |user_id |name |date    |time1 |time2|
 +---+--------+-----+--------+------+-----+
 |2  |1       |x    |12-1-15 |10:30 |21:30|
 |3  |2       |y    |12-1-15 |10:30 |22:30|
 |4  |1       |x    |13-1-15 |10:30 |18:30|
 |6  |2       |y    |13-1-15 |10:30 |20:30|
 +---+--------+-----+--------+------+-----+

SELECT * FROM user
Where Date In (Select Max(Date) from user 
Group by user_id,Date)

- Vam

---

不要使用IN()，而是使用INNER JOIN。 - Paul Maxwell

@Used_By_Already 不，INNER JOIN 在大量数据的情况下是一个大问题。IN 子句只选择必要的行。 - Mike

IN() 比 INNER JOIN 更慢，所以请继续使用 IN()，但请不要抱怨性能。 - Paul Maxwell

Answer 1

这个查询：

select
    u.*
from user u
inner join (
    select
      user_id, `date`, max(time2) as maxtime2
    from user
    group by user_id, `date`
  ) mx on u.user_id = mx.user_id and u.`date` = mx.`date` and u.time2 = mx.maxtime2

生成与预期输出相匹配的结果。

| id | user_id | name |                      date | time1 | time2 |
|----|---------|------|---------------------------|-------|-------|
|  2 |       1 |    x | January, 12 2015 00:00:00 | 10:30 | 22:30 |
|  3 |       2 |    y | January, 12 2015 00:00:00 | 10:30 | 22:30 |
|  4 |       1 |    x | January, 13 2015 00:00:00 | 10:30 | 18:30 |
|  6 |       2 |    y | January, 13 2015 00:00:00 | 10:30 | 20:30 |

如果只是查看日期（不涉及时间）：

select
    u.*
from user u
inner join (
    select
      user_id, max(`date`) as maxdate
    from user
    group by user_id
  ) mx on u.user_id = mx.user_id and u.`date` = mx.maxdate

但是使用示例数据进行查询的结果是：

| id | user_id | name |                      date | time1 | time2 |
|----|---------|------|---------------------------|-------|-------|
|  4 |       1 |    x | January, 13 2015 00:00:00 | 10:30 | 18:30 |
|  5 |       2 |    y | January, 13 2015 00:00:00 | 10:30 | 18:30 |
|  6 |       2 |    y | January, 13 2015 00:00:00 | 10:30 | 20:30 |

如果需要考虑时间，则说明您的设计存在问题，因为您将时间与日期分开了。为了得到最大日期/时间，需要像以下这样添加列：

select
    u.*
from user u
inner join (
    select
      user_id, max(addtime(`date`, time2)) as maxdatetime
    from user
    group by user_id
  ) mx on u.user_id = mx.user_id and addtime(u.`date`, u.time2) = mx.maxdatetime

从样本数据中可以得出以下结果：

| id | user_id | name |                      date | time1 | time2 |
|----|---------|------|---------------------------|-------|-------|
|  4 |       1 |    x | January, 13 2015 00:00:00 | 10:30 | 18:30 |
|  6 |       2 |    y | January, 13 2015 00:00:00 | 10:30 | 20:30 |

- Paul Maxwell