mySQL 查询返回每个表的最后一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9115059/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mySQL query to return last record for each table
提问by Alberto acepsut
I have a mySQl db (name "stocks") with 50 tables, each tables with
我有一个带有 50 个表的 mySQl db（名称“股票”），每个表都有
id, symbol, date, time, open, high, low, close, volume as columns (9 columns).
id、交易品种、日期、时间、开盘价、最高价、最低价、收盘价、成交量作为列（9 列）。
I would like to know what is the last record for each table, ordered for date then time.
我想知道每个表的最后一条记录是什么，按日期和时间排序。
Should I have to ORDER BY all data for each table or there is a better way to just know last record?
我应该按每个表的所有数据排序还是有更好的方法来知道最后一条记录？
I am asking help for a query that just return only last record for each table in db.
我正在寻求一个查询的帮助，该查询只返回数据库中每个表的最后一条记录。
Thanks
谢谢
PS For last record I mean most recent as Date then Time
PS对于最后一条记录，我的意思是最近的日期然后是时间
回答by Vyktor
There are two options how to do that:
有两种方法可以做到这一点：
-- I would use this only if you need more than one records
SELECT * FROM table ORDER BY date DESC LIMIT 1;
-- Way to go:
SELECT * FROM table WHERE date = (SELECT MAX(date) FROM table) LIMIT 1;
Don't forget to add index on date. If it's possible you add lot's of records at the same time you will have to add:
不要忘记在 上添加索引date。如果可以同时添加大量记录，则必须添加：
ORDER BY id DESC -- In case that date is highest for records for last records
ORDER BY time DESC -- Every other case
To the end of query
到查询结束
回答by FrustratedWithFormsDesigner
I am going to make the assumption that the record with the largest ID is the "last" (assuming strictly increasing sequential IDs that are unique within a table). If you have a better definition of "last" that could make a difference.
我将假设具有最大 ID 的记录是“最后一个”（假设严格增加在表中唯一的顺序 ID）。如果您对“最后”有更好的定义，那可能会有所作为。
To get one "last" record, you could do:
要获得一个“最后”记录，您可以执行以下操作：
Select * from table_1 where id = (select max(id) from table_1);
To get the results of all 50 tables into a single result set, you could do:
要将所有 50 个表的结果放入一个结果集中，您可以执行以下操作：
Select * from table_1 where id = (select max(id) from table_1)
union
Select * from table_2 where id = (select max(id) from table_2)
union
Select * from table_3 where id = (select max(id) from table_3)
union...
A MySQL-specific solution could be
特定于 MySQL 的解决方案可能是
Select * from table_1 order by id desc limit 1
union
Select * from table_2 order by id desc limit 1
union
Select * from table_3 order by id desc limit 1
union...
Based on your edit (where you actually define what you mean by "last"):
根据您的编辑（您实际定义“最后”的含义）：
Select * from table_1 order by date desc, time desc, id desc limit 1
union
Select * from table_2 order by date desc, time desc, id desc limit 1
union
Select * from table_3 order by date desc, time desc, id desc limit 1
union...
回答by wingedhorse
Here is one way to do it without sorting the table:
这是一种无需对表格进行排序的方法：
select * from tab1
where time = (select max(time)
from tab1
where date = (select max(date) from tab1))
and date = (select max(date) from tab1)
It should be very fast, like, O(c), provided that both columns are indexed, otherwise the time will simply be O(n)
它应该非常快，比如 O(c)，前提是两列都被索引，否则时间将是 O(n)
