MySQL 选择每个 id 具有最新值的所有行

Question

我有一个看起来像这样的表:

| id | user | data |
--------------------
| 1  |  11  | aaa1 |
| 2  |  11  | aaa2 |
| 3  |  11  | aaa3 |
| 4  |  22  | aaa4 |
| 5  |  33  | aaa5 |
| 6  |  33  | aaa6 |
| 7  |  44  | aaa7 |

我想选择包含所有数据的所有行，并且我想为每个 user 添加具有 max id 的数据，应该如下所示:

| id | user | data | f_id | f_data |
------------------------------------
| 1  |  11  | aaa1 |   3  |  aaa3  |
| 2  |  11  | aaa2 |   3  |  aaa3  |
| 3  |  11  | aaa3 |   3  |  aaa3  |
| 4  |  22  | aaa4 |   4  |  aaa4  |
| 5  |  33  | aaa5 |   6  |  aaa6  |
| 6  |  33  | aaa6 |   6  |  aaa6  |
| 7  |  44  | aaa7 |   7  |  aaa7  |

这是我的查询尝试:

SELECT  t1.*, t2.id AS f_id, t2.data AS f_data
        FROM table1 t1
        LEFT JOIN table1 t2
             ON t1.user=(SELECT MAX(t2.id)
                         FROM table1 t2
                         WHERE t2.user = t1.user )

更新:所有的答案都是正确的，但是当我在 80K+ 行的表上运行查询时，MySQL 需要很多时间来执行查询。对于我的项目，我将添加 ajax，以便用户可以单击它，php 将在每次单击时执行一行查询。

Answer 1

SQL Fiddle Demo

SELECT  t1.*, 
        t2.id AS f_id, 
        t2.data AS f_data
FROM table1 t1
JOIN table1 t2
  ON t1.user = t2.user
 AND t2.id = (SELECT MAX(t2.id)
              FROM table1 t3
              WHERE t3.user = t1.user)