SQL将表与组连接到其自身

Question

我有一个表，我想按它按ID分组，但同时，我想用ID和日期创建另一个组，然后用ID分组将这个表连接到表中。示例代码：

下面是如何创建表，我希望让JOIN使用ID和日期来获得ORG表上的第一个和最后一个。

SELECT
    ID,
    MIN([DATE]) AS MIN_DATE,
FROM [ORG] AS ORG
GROUP BY ID
    
SELECT
    ID, 
    [DATE],
    MIN(EXEC_AS_OF_TIME) AS [FIRST],
    MAX(EXEC_AS_OF_TIME) AS [LAST]
FROM [ORG] AS TEMP
GROUP BY ID, [DATE]

我是这么想的，但事实并非如此。我哪里出问题了？

SELECT
    ORG.ID,
    MIN([ORG.DATE]) AS MIN_DATE,
    TEMP.[FIRST],
    TEMP.[LAST]
FROM [ORG] AS ORG
GROUP BY ID
LEFT JOIN (
    SELECT
        ID, 
        [DATE],
        MIN(EXEC_AS_OF_TIME) AS [FIRST],
        MAX(EXEC_AS_OF_TIME) AS [LAST]
    FROM [TEMP]
    GROUP BY ID, [DATE]
) AS TEMP ON ORG.ID = TEMP.ID AND ORG.[MIN_DATE] = TEMP.[DATE]

Answer 1

你需要修改语法如下-

select org.id, MIN_DATE,FIRST,LAST
from
(
SELECT ORG.ID,MIN([ORG.DATE]) AS MIN_DATE
FROM [ORG] GROUP BY ID) AS ORG
LEFT JOIN
(
  SELECT ID, [DATE],
    MIN(EXEC_AS_OF_TIME) AS [FIRST],MAX(EXEC_AS_OF_TIME) AS [LAST]
FROM [ORG]
GROUP BY ID, [DATE]) AS TEMP ON ORG.ID = TEMP.ID AND ORG.[MIN_DATE] = TEMP.[DATE]

Answer 2

您不需要对此进行如此复杂的查询。只需使用窗口函数：

SELECT id.*
FROM (SELECT ID, [DATE],
             MIN(EXEC_AS_OF_TIME) AS [FIRST],
             MAX(EXEC_AS_OF_TIME) AS [LAST],
             MIN([DATE]) OVER (PARTITION BY ID) as MIN_DATE
      FROM [ORG] AS TEMP
      GROUP BY ID, [DATE]
     ) id
WHERE [DATE] = min_date;