我正在处理大量数据。我想选择每个用户每个日期最新记录。我的表格如下:
+---+--------+-----+--------+------+-----+
|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)
这个查询:
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 |
如果只看date(不考虑时间):
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 |