SQL Server group maximum, minimum, maximum corresponding time, and minimum value corresponding time

Create a database First

CREATE TABLE [dbo]. [Students] (
[ID] [int] IDENTITY (*) Not NULL,
[Age] [INT] Null
[Name] [nvarchar] () NULL,
[Addtime]  [DateTime] Null
) on [PRIMARY]

Insert several test data

INSERT [dbo]. [Students] ([age], [name], [Addtime]) VALUES (N ' John Doe ', ' 2015-04-08 01:00:00.000 ')
INSERT [dbo]. [Students] ([age], [name], [Addtime]) VALUES (8, N ' John Doe ', ' 2017-05-03 00:00:00.000 ')
INSERT [dbo]. [Students] ([age], [name], [Addtime]) VALUES (98, N ' John Doe ', ' 2017-10-03 00:00:00.000 ')
INSERT [dbo]. [Students] ([age], [name], [Addtime]) VALUES (N ' Zhang San ', ' 2016-09-08 00:00:00.000 ')
INSERT [dbo]. [Students] ([age], [name], [Addtime]) VALUES (N ' Zhang San ', ' 2011-05-08 00:00:00.000 ')
INSERT [dbo]. [Students] ([age], [name], [Addtime]) VALUES (5, N ' Zhang San ', ' 2014-04-01 00:00:00.000 ')

The first type of notation:

This notation uses the window function, the behavior Description of the window function appears in the function's over clause, and involves multiple elements, 3 core elements are: partition, sort and frame

SELECT DISTINCT name,
MaxAge, Max (case maxagenum if 1 then addtime else "end") over (partition by name) Maxaddtime,
Minage,max (case minagenum if 1 then addtime else "end" over (partition by name) Minaddtime
From (
Select Name,addtime,
Max (age) over (partition by name) MaxAge,
MIN (age) over (partition by name) Minage,
RANK () over (partition by name ORDER BY age desc) Maxagenum,
RANK () over (partition by name order by age) Minagenum from students
) s

The second way:

With S as
(
Select Name,max (age), maxage,min (age), minage from students
Group BY name
)
Select Name,max (MaxAge) Maxage,max (maxagetime) Maxagetime,max (minage) Minage,max (minagetime) Minagetime from (
Select Ss.name,s.maxage,ss.addtime maxagetime,0 minage, ' minagetime from students SS INNER join s on Ss.name=s.name and Ss.age=s.maxage
UNION ALL
Select ss.name,0 maxAge, ' Maxagetime,s.minage minage,ss.addtime minagetime from students SS INNER join s on SS.NAME=S.N Ame and Ss.age=s.minage
) a GROUP by name

Results such as:

SQL Server group maximum, minimum, maximum corresponding time, and minimum value corresponding time