mysql - 如何为特定用户选择最后一行？

Question

我有一个这样的表:

// requests
+----+----------+-------------+
| id | id_user  |  unix_time  |
+----+----------+-------------+
| 1  | 2353     | 1339412843  |
| 2  | 2353     | 1339412864  |
| 3  | 5462     | 1339412894  |
| 4  | 3422     | 1339412899  |
| 5  | 3422     | 1339412906  |
| 6  | 2353     | 1339412906  |
| 7  | 7785     | 1339412951  |
| 8  | 2353     | 1339413640  |
| 9  | 5462     | 1339413621  |
| 10 | 5462     | 1339414490  |
| 11 | 2353     | 1339414923  |
| 12 | 2353     | 1339419901  |
| 13 | 8007     | 1339424860  |
| 14 | 7785     | 1339424822  |
| 15 | 2353     | 1339424902  |
| 16 | 2353     | 1466272801  |
| 17 | 2353     | 1466272805  |
+----+----------+-------------+

我正在尝试获取与特定用户相关的最后一行。例如对于这个用户 id_user = 7785 我想选择这一行:

| 14 | 7785     | 1339424822  |

这是我的查询:

SELECT unix_time AS last_seen
FROM requests WHERE id = '7785'
ORDER BY unix_time DESC
LIMIT 1

但是我的查询没有选择任何行。怎么了？

SQL fiddle

---

另请注意，您能否告诉我应该在 id_user 和 uinx_time 上创建单个索引还是应该在这两列上创建多列索引 (id_user, unix_time)?

Answer 1

您使用了错误的列(id 而不是 id_user — 反之亦然):

SELECT unix_time AS last_seen
FROM requests WHERE id_user = '7785'
ORDER BY unix_time DESC
LIMIT 1