如何基于SQL中的不同功能在不同字段上实现多个连接

Question

我的表格很少。而且，我需要根据每个ID和Type列的每个最大级别和最新级别(按日期订购(根据每个最大级别> 进行记录。我正在使用SQL Server运行查询。到目前为止，我尝试了以下SQL查询：

select f.ID,x.MAX_LEVEL,f.TYPE, f.DATE
from (
 select ID
,TYPE
, MAX(LEVEL) as MAX_LEVEL
 from TABLEA
 GROUP BY ID, TYPE
 ) as x 
  ,
 (
  select ID
,TYPE
, MAX(DATE) as MAX_DATETIME
 from TABLEA
 GROUP BY ID, TYPE
 ) as y
 inner join TABLEA as f  
 on f.ID = x.ID and f.LEVEL = x.MAX_LEVEL
 inner join TABLEA as g 
 on f.ID = y.ID and g.DATE = y.MAX_DATETIME
 and f.DATE > DATEADD(day, -1, GETDATE())

tablea

ID  TYPE    LEVEL   DATE
1   ELECTRIC    2   01/06/2019
1   GAS         2   01/06/2019
2   ELECTRIC    2   01/06/2019
3   ELECTRIC    3   01/06/2019
3   ELECTRIC    3   01/06/2019
1   GAS         3   05/06/2019
1   GAS         5   13/06/2019
2   ELECTRIC    5   07/06/2019
3   GAS         5   08/06/2019
6   ELECTRIC    3   02/06/2019
2   ELECTRIC    3   04/06/2019
3   ELECTRIC    3   05/06/2019
2   GAS         10  06/06/2019
2   GAS         3   11/06/2019
3   ELECTRIC    3   11/06/2019
1   ELECTRIC    5   01/06/2019
1   GAS         3   02/06/2019
6   ELECTRIC    5   01/06/2019
1   ELECTRIC    5   10/06/2019

预期结果：

ID  TYPE    MAX_LEVEL   LATEST_LEVEL
1   ELECTRIC    5       5
1   GAS         5       3
2   ELECTRIC    5       5
2   GAS         10      3
3   ELECTRIC    3       3
3   GAS         5       5
6   ELECTRIC    5       3

有什么想法，我该如何实现？

Answer 1

如果您使用的是SQLServer，则可以尝试。

SELECT ID, TYPE, MAX(T1.[LEVEL]) AS MAX_LEVEL, X.LEVEL AS LATEST_LEVEL
FROM TABLEA T1
    OUTER APPLY (SELECT TOP 1 [LEVEL] FROM TABLEA T2 WHERE T2.ID = T1.ID AND T2.TYPE = T1.TYPE ORDER BY T2.[DATE] DESC) X
GROUP BY ID, TYPE, X.[LEVEL] 
ORDER BY ID, TYPE

Answer 2

不幸的是，SQL Server没有"第一个"或"最后"聚合函数。但是它确实具有first_value()和last_value()作为窗口函数。因此，一种方法是：

select distinct t.id, t.type
       max(t.level) over (partition by id, type) as max_level,
       first_value(t.level) over (partition by id, type order by date desc) as latest_level
from t;

另一种选择是在子查询中使用窗口函数：

select id, type, max(level) as max_level,
       max(case when seqnum = 1 then level end) as latest_level
from (select t.*,
             row_number() over (partition by id, type order by date desc) as seqnum
      from t
     ) t
group by id, type;