时间戳

首页 > 解决方案 > SQL中如何根据不同的功能实现不同字段的多个join？

sql - SQL中如何根据不同的功能实现不同字段的多个join？

问题描述

我有几个表如下。而且，我需要根据每个和列的每个最大级别和最新级别（按日期排序）来获取记录。我正在使用 SQL Server 运行查询。到目前为止，我已经尝试了以下 SQL 查询：IDType

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())

表

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

有什么想法，我怎么能做到这一点？

标签： sqlsql-serverleft-joininner-join

解决方案

如果你使用的是 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

推荐阅读