在同一个SQL查询中选择max和min值

I have a SQL table with the columns: ID, ADate, XValue and Type. For each "Type" value, I'd like to retrieve the most recent row (per ADate), the least recent row (per ADate) and also the row containing the highest "XValue" value.

我有一个SQL表，列有:ID、ADate、XValue和Type。对于每个“Type”值，我希望检索最近的行(每个ADate)、最近的行(每个ADate)以及包含最高“XValue”值的行。

Is it possible to be performed through a single SQL query? I've tried a few times with no success.

是否可以通过单个SQL查询执行?我试过几次都没有成功。

3 个解决方案

#1

select ID, ADate, XValue,[Type]
from (
  select *
     ,row_number() over (partition by type order by adate asc) AS DateSeq
     ,row_number() over (partition by type) AS TypeCount
     ,row_number() over (partition by type order by xvalue desc) AS ValueSeq
  from t
  ) as tt
where tt.DateSeq=1 OR tt.DateSeq=tt.TypeCount OR tt.ValueSeq=1

#2

Using row_number() you could do something like this:

使用row_number()可以做以下事情:

select ID, ADate, XValue and Type
from (
  select *
    , min_adate  = row_number() over (partition by type order by adate asc)
    , max_adate  = row_number() over (partition by type order by adate desc)
    , max_xvalue = row_number() over (partition by type order by xvalue desc)
  from t
  ) as sub
where 1 in (min_adate,max_adate,max_xvalue)
/* --also could be written as 
where min_adate = 1
   or max_adate = 1
   or max_xvalue = 1
*/

Using a common table expression may make it more readable.

使用通用的表表达式可以使其更具可读性。

;with cte as (
  select *
    , min_adate  = row_number() over (partition by type order by adate asc)
    , max_adate  = row_number() over (partition by type order by adate desc)
    , max_xvalue = row_number() over (partition by type order by xvalue desc)
  from t
)
select ID, ADate, XValue and Type
from cte
where 1 in (min_adate,max_adate,max_xvalue)

#3

One method uses row_number():

一种方法是使用row_number():

select t.*
from (select t.*,
             row_number() over (partition by type order by aDate desc) as seqnum_adate_desc,
             row_number() over (partition by type order by aDate asc) as seqnum_adate_asc,
             row_number() over (partition by type order by XValue desc) as seqnum_xvalue_desc
      from t
     ) t
where 1 in (seqnum_adate_desc, seqnum_adate_asc, seqnum_xvalue_desc);

#1

select ID, ADate, XValue,[Type]
from (
  select *
     ,row_number() over (partition by type order by adate asc) AS DateSeq
     ,row_number() over (partition by type) AS TypeCount
     ,row_number() over (partition by type order by xvalue desc) AS ValueSeq
  from t
  ) as tt
where tt.DateSeq=1 OR tt.DateSeq=tt.TypeCount OR tt.ValueSeq=1

#2