我想选择MySQL中有5个项目的特定表的最新行.
该表看起来像：
数据类似于：
|id | to | from | time stamp | text
| 1 | user01 | user02 | 2011-09-01 | text1
| 2 | user01 | user02 | 2011-09-02 | text2
| 3 | user02 | user01 | 2011-09-02 | text3
| 4 | user01 | user03 | 2011-09-03 | text4
| 5 | user01 | user04 | 2011-09-03 | text5
| 6 | user01 | user03 | 2011-09-04 | text6
| 7 | user03 | user01 | 2011-09-05 | text7
我想选择* WHERE to =’user01’和最新数据(可能是“id”或“时间戳”). “from”可以很多,但每个相同的“from”数据只能出现一次.
无论如何,所选数据将是：
| 2 | user01 | user02 | 2011-09-02 | text2
| 5 | user01 | user04 | 2011-09-03 | text5
| 6 | user01 | user03 | 2011-09-04 | text6
可以吗？
感谢您花时间阅读我的问题:)
最佳答案
SELECT t.*
FROM
TableX AS t
JOIN
( SELECT DISTINCT `from` AS f
FROM TableX
WHERE `to` = 'user01'
) AS df
ON
t.id = ( SELECT tt.id
FROM TableX AS tt
WHERE tt.`to` = 'user01'
AND tt.`from` = df.f
ORDER BY tt.`timestamp` DESC
LIMIT 1
)
最好避免使用to,from和timestamp等关键字命名表和字段.