Sql-Server
將列與另一列的聚合連接起來
我想要的結果如下。我不能使用 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 ,....