mysql查询不同并排序

Question

我有一个具有这种结构的 table_A:

|- id_a primary key
|- id_b references table_B(id_b)
|- timestamp
|- varchar_column

我需要做一个连接表_B 的查询，它在表_A.id_b 上必须是不同的，但它还必须返回其他列(不仅是不同的列)；我还需要为每个不同的 table_a.id_b 获取具有最大时间戳的记录。我确实喜欢两种方式，但它们都返回错误:

SELECT DISTINCT(table_A.id_b) FROM table_A INNER JOIN table_B on table_A.id_b = table_B.id_b ORDER BY table_A.timestamp DESC

还有这个:

SELECT * FROM table_A INNER JOIN table_B on table_A.id_b = table_B.id_b ORDER BY table_A.timestamp DESC GROUP BY table_A.id_b

Answer 1

您可以在 table_a 上对 id_b 和 max(timestamp) 使用内部联接并联接其余表

select * 
from table_A a
Inner join (
    select id_b, max(timestamp) my_time
    from table_A
    group by id_b
) t on t.id_b = a.id_b and t.my_time = a.timestamp
inner join table_B on a.id_b = table_B.id_b 
ORDER BY a.timestamp DESC