MySQL 从mysql表的一列中选择最大值、最小值、最后一个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18748830/
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
Select max,min, last value from a column in mysql table
提问by G Jay
table format
表格格式
id time_stamp
3 2013-09-05 12:00:00
5 2013-09-06 12:00:00
12 2013-09-07 12:00:00
2 2013-09-08 12:00:00
5 2013-09-09 12:00:00
8 2013-09-10 12:00:00
From the above table i want select min(id), max(id), last id, last time_stamp in single mysql select query statement
从上表中，我想在单个 mysql 选择查询语句中选择 min(id)、max(id)、last id、last time_stamp
Needed output is:
需要的输出是：
min max last(val) last(time_stamp)
2 12 8 2013-09-09 12:00:00
i used following Query
我使用了以下查询
select id, min(id),max(id), time_stamp from table order by time_stamp limit 1
I am getting wrong latest id value 3 instead of 8
我得到错误的最新 id 值 3 而不是 8
Check this if below SQL fiddle
http://www.sqlfiddle.com/#!2/e9cb1/2/0
如果低于 SQL fiddle http://www.sqlfiddle.com/#!2/e9cb1/2/0，请检查这个
回答by Gordon Linoff
Here is a method, using the substring_index()/group_concat()trick:
这是一种使用substring_index()/group_concat()技巧的方法：
select min(id), max(id),
substring_index(group_concat(id order by time_stamp desc), ',', 1) as lastid
from table ;
回答by Praveen Lobo
Assuming you mentioned the last(time_stamp) incorrectly in your question- Get the max, min as usual and then find out the last id and time stamp in a subquery which you can then JOIN to get all result in one row.
假设您在问题中错误地提到了 last(time_stamp)- 像往常一样获取最大值、最小值，然后在子查询中找出最后一个 id 和时间戳，然后您可以 JOIN 以在一行中获取所有结果。
MySQL 5.5.32 Schema Setup:
MySQL 5.5.32 架构设置：
create table t (id int, time_stamp datetime);
insert into t values(3, '2013-09-05 12:00:00');
insert into t values(5, '2013-09-06 12:00:00');
insert into t values(12, '2013-09-07 12:00:00');
insert into t values(2, '2013-09-08 12:00:00');
insert into t values(5, '2013-09-09 12:00:00');
insert into t values(8, '2013-09-10 12:00:00');
Query 1:
查询 1：
SELECT MIN(t.id), MAX(t.id), latest.id, latest.time_stamp
FROM t JOIN (
SELECT t.id, t.time_stamp
FROM t
ORDER BY time_stamp DESC
LIMIT 1) latest
结果：
| MIN(T.ID) | MAX(T.ID) | ID | TIME_STAMP |
|-----------|-----------|----|----------------------------------|
| 2 | 12 | 8 | September, 10 2013 12:00:00+0000 |
