我想选择MySQL中有5个项目的特定表的最新行。表格看起来像：
- id(自动增加(
- 至
- 来自
- 时间戳
- 文本
数据类似于：
|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
我想要select * 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来命名表和字段。
SELECT * FROM tablename WHERE to = 'user01' ORDER BY timestamp DESC LIMIT 1
会给你最新的参赛作品。
我想你在找SELECT DISTINCT user_from FROM table WHERE user_to='user1' ORDER BY id DESC？
DISTINCT将只为每个user_from值返回一行。
如果您想要每个唯一from用户的最新一行：
SELECT `from`, MAX(`id`), `to` FROM `tablename` WHERE `to`='user01' GROUP BY `from`
(我认为获取id的最大值可能比时间戳的最大值更快(
您可以这样做：
SELECT to, from, max(timestamp) FROM <table> WHERE to = 'user01' GROUP BY from
要获得相关的文本，你也可以这样做，但这不是很有效的
SELECT * FROM <table>
WHERE
CONCAT([to], [from], [timestamp])
IN
(
SELECT CONCAT([to], [from], MAX([timestamp])) FROM <table>
WHERE [to] = 'user01' GROUP BY [from]
)
一种可能是这样的：
SELECT * FROM tbl WHERE id IN (
SELECT MAX(id) FROM tbl
WHERE to = 'user01'
GROUP BY from
);
但我不知道它是否符合你的要求，每个"from"只能出现一次。
SELECT * FROM `table_name` WHERE `to` = 'user01' ORDER BY `timestamp` DESC LIMIT 0,1