將列與另一列的聚合連接起來

這是我的桌子。

我想要的結果如下。我不能使用 where stage = ? 因為該列值可以是任何值。

我嘗試使用此查詢，但它返回空值。

select case when [date] =(select  max(date) from #temp) then stage end as stages from #temp

下面是我想使用最小值和最大值的完整查詢

SELECT opportunity.OpportunityId,
opportunity.ClientBusinessGuid as ClientID
, clientbusiness.BusinessName as ClientName
, otypes.[Name] as [Opportunity Type]
, choices.[Text] as [Source]
,opportunity.DateCreated
,DateName(month,opportunity.DateCreated) as CohortMonth
,opportunity.ExpectedDate as [ExpectedCloseDate(start of period)]
,history.[ExpectedCloseDate(end of period)]
,case when history2.[date] = (select min(history2.[date]) from #temp2 where ---HERE
history2.OpportunityId = opportunity.OpportunityId) THEN history2.Stage END
FROM MYP_Opportunity opportunity
JOIN MYP_ClientBusinesses_X clientbusiness ON opportunity.ClientBusinessGuid = 
clientbusiness.ClientBusinessGuid
JOIN MYP_OpportunityTypes otypes ON otypes.OpportunityTypeId = opportunity.TypeId
JOIN MYP_Choices choices ON opportunity.SourceId = choices.ChoiceId
JOIN #temp history ON history.OpportunityId = opportunity.OpportunityId
JOIN #temp2 history2 ON history2.OpportunityId = opportunity.OpportunityId
AND clientbusiness.AdviserbusinessId = 66
--AND opportunity.OpportunityId = 180643
where history.HistoryId = (Select MAX(HistoryId) from #temp where history.OpportunityId = 
opportunity.OpportunityId)
group by opportunity.ClientBusinessGuid, clientbusiness.BusinessName, otypes.[Name] ,choices. 
[Text] ,opportunity.DateCreated, opportunity.OpportunityId
,opportunity.ExpectedDate,history.[ExpectedCloseDate(end of 
period)],history2.OpportunityId,history2.Stage,history2.Date

您可以在歷史表上使用交叉應用來根據機會 id（帶階段）根據最小或最大日期獲取整行（最小日期為升序，最大日期為降序 = 2 交叉應用）？

就像是：

select 
   t1.opportunity 
   ,MinDate.minimumDate
   ,MaxDate.maximumDate
from #temp2 t1
cross apply --outer?
(
   select top 1
       concat(date, stage) as minimumDate
   from #temp2 t2
   where t2.opportunity = t1.opportunity
   ...
   order by date asc
)MinDate
cross apply
(
   select top 1
       concat(date,stage) as maximumDate
   from #temp2 t2
   where t2.opportunity = t1.opportunity
   ...
   order by date desc
)MaxDate
group by
   t1.opportunity
   ,MinDate.minimumDate
   ,MaxDate.maximumDate
   ,....

引用自：https://dba.stackexchange.com/questions/316262