php - 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)

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 |