sql server中基于分区的min()和max() [英] Min() and Max() based on partition in sql server

问题描述

我想使用 min &最大功能，但在某些条件下.

I want to use min & max function but on certain criteria.

Create Table #Test (Id Int Identity(1,1), Category Varchar(100), DateTimeStamp DateTime)



    Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 01:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 02:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 03:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 04:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 05:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 06:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 07:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 08:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 09:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 10:00:13.503')  
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 11:00:13.503')

带有输出的当前查询

select category, min(DateTimeStamp) as minn , max(DateTimeStamp) as maxx from #Test
group by category

电流输出

预期产出

推荐答案

你可以试试下面的 - 这是一个差距 &岛屿问题

You can try below - it's a gap & island problem

演示

select category, min(datetimestamp),max(datetimestamp)
from
(
select *,row_number() over(order by datetimestamp) -
row_number() over(partition by category order by datetimestamp) as rn2
from #Test
)A group by category,rn2 order by 2

输出:

category       minval               maxval
c1             13/08/2019 01:00:13  13/08/2019 05:00:13
c2             13/08/2019 06:00:13  13/08/2019 10:00:13
c1             13/08/2019 11:00:13  13/08/2019 11:00:13

这篇关于sql server中基于分区的min()和max()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！