我有一个名为"lift"的表:
-----------------------------------------------------
| ID | USERNAME | lift_ID | DATE |
-----------------------------------------------------
| 1 | user1 | 0 | 2013-06-01 |
-----------------------------------------------------
| 2 | user1 | 0 | 2013-06-03 |
-----------------------------------------------------
| 3 | user1 | 1 | 2013-06-03 |
-----------------------------------------------------
| 4 | user2 | 0 | 2013-06-04 |
-----------------------------------------------------
| 5 | user2 | 1 | 2013-06-04 |
-----------------------------------------------------
| 6 | user2 | 1 | 2013-06-05 |
-----------------------------------------------------
| 7 | user1 | 2 | 2013-06-03 |
我想从某个用户中选择所有不同的lift_id，其中日期是该用户的lift_id的最近日期。
如果我对user1这样做，我将得到:
-----------------------------------------------------
| 2 | user1 | 0 | 2013-06-03 |
-----------------------------------------------------
| 3 | user1 | 1 | 2013-06-03 |
-----------------------------------------------------
| 7 | user1 | 2 | 2013-06-03 |
I've try:
SELECT *
FROM lift l1
WHERE date` = (
SELECT MAX(date)
FROM lift
WHERE l1.lift_id = lift.lift_id)
AND username = 'user1'
ORDER BY lift_id ASC
)
;
这个select是所有不同的升降机在最大日期，但没有考虑到不同的用户，这是我想要的
查询已接近。您需要在子查询中添加一个关联子句:
SELECT l.*
FROM lift l
WHERE l.date = (select max(l2.date)
from lift l2
where l2.lift_id = l.lift_id and
l2.username = l.username
) and
l.username = 'user1'
ORDER BY l.lift_id ASC;
您可以使用NOT EXISTS():
SELECT t.* FROM lift t
WHERE NOT EXISTS(SELECT 1 FROM lift s
WHERE s.lift_id = t.lift_id
AND s.username = t.username
AND s.date > t.date)