使用 SQL 从表中查找指定记录的最早和最晚日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2514189/
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
Find earliest and latest dates of specified records from a table using SQL
提问by tonyyeb
I have a table (in MS SQL 2005) with a selection of dates. I want to be able to apply a WHERE statement to return a group of them and then return which date is the earliest from one column and which one is the latest from another column. Here is an example table:
我有一个包含日期选择的表（在 MS SQL 2005 中）。我希望能够应用 WHERE 语句来返回一组它们，然后从一列返回哪个日期最早，哪个是另一列中的最新日期。这是一个示例表：
ID StartDate EndDate Person
1 01/03/2010 03/03/2010 Paul
2 12/05/2010 22/05/2010 Steve
3 04/03/2101 08/03/2010 Paul
So I want to return all the records where Person = 'Paul'. But return something like (earliest ) StartDate = 01/03/2010 (from record ID 1) and (latest) EndDate = 08/03/2010 (from record ID 3).
所以我想返回 Person = 'Paul' 的所有记录。但返回类似（最早）StartDate = 01/03/2010（来自记录ID 1）和（最新）EndDate = 08/03/2010（来自记录ID 3）。
Thanks in advance
提前致谢
回答by Richard
You need the minand maxaggregate functions, e.g. a very simple case:
您需要min和max聚合函数，例如一个非常简单的案例：
select min(StartDate), max(EndDate)
from data
where Person = 'Paul'
You have all the usual power of SQL, so selection from a sub-query is available.
您拥有 SQL 的所有常用功能，因此可以从子查询中进行选择。
回答by Whakkee
It would be neat to use a group byas well. So If you like all the persons in your result, and you leave out the where clause, you wouldn't get erroneous data:
使用 agroup by也很好。因此，如果您喜欢结果中的所有人，并且省略 where 子句，则不会得到错误数据：
select person, min(StartDate), max(EndDate)
from data
group by person
or
或者
select person, min(StartDate), max(EndDate)
from data
where person ='Paul'
group by person
or
或者
select person, min(StartDate), max(EndDate)
from data
group by person
having person ='Paul'
