SQL Server finds the maximum value, minimum value, time corresponding to the maximum value, and time corresponding to the minimum value of the group

This article explains the knowledge related to sql server's groupingmaximum value, minimum value, time corresponding to the maximum value, and time corresponding to the minimum value.

Create the Students table first

CREATE TABLE [dbo].[Students](
[Id] [int] IDENTITY(1,1) NOT NULL,
[age] [int] NULL,
[name] [nvarchar](50) NULL,
[addTime] [datetime] NULL
) ON [PRIMARY]

INSERT Several pieces of test data

INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (22, N'李思', '2015-04-08 01:00 :00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (8, N'李思', '2017-05-03 00:00:00.000' )
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (98, N'李思', '2017-10-03 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (34, N'Zhang San', '2016-09-08 00:00:00.000')
INSERT [ dbo].[Students] ([age], [name], [addTime]) VALUES (45, N'张三','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 way of writing:

This way of writing uses the window function . The behavior description of the window function appears in the OVER clause of the function and involves multiple elements, 3 core elements. They are: partition, sorting and framework

select distinct name,
maxAge, max(case maxAgenum when 1 then addtime else '' end) over(partition by name) maxAddTime,
minage,max(case minAgenum when 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

Second way of writing:

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.name and ss.age=s.minAge
) a group by name

This article explains how to find the maximum value, minimum value, corresponding time of the maximum value, and time corresponding to the minimum value in sql server. For more learning materials, please pay attention to the PHP Chinese website.

Related recommendations:

Related explanations about left join on and where condition placement

About php mysql fuzzy query function Related knowledge

#How to copy and move files through php

The above is the detailed content of SQL Server finds the maximum value, minimum value, time corresponding to the maximum value, and time corresponding to the minimum value of the group. For more information, please follow other related articles on the PHP Chinese website!