编写SQL查询当前PTID与Next_PTID的问题求助

需要编写SQL语句，展示指定TeamPlanningID对应的当前PTID和Next_PTID：

• 当前PTID：对应关联表中Start Date非空且End Date为空的PTID
• Next_PTID：对应关联表中Start Date为空且End Date为空的PTID

select 
  a.TeamPlanningID,
  case when b.[Start Date] is not null and [End Date] is null then a.[PTID] END as [PTID]   
 ,case when b.[Start Date] is null and [End Date] is null then a.[PTID] END as Next_PTID    
from  
  [TableA] a 
inner join [TableB] c on a.ConsultantsRefDataIDs = b.ID
where a.TeamPlanningID = 36

1. 关联表别名错误：inner join [TableB] c却引用b.ID进行关联，别名不匹配
2. 原语句会返回多行分散数据，无法将当前PTID和Next_PTID合并到同一行展示

方案1：条件聚合（推荐） #

通过聚合函数将两个PTID值合并到同一行：

select 
  a.TeamPlanningID,
  max(case when b.[Start Date] is not null and b.[End Date] is null then a.[PTID] END) as [当前PTID],
  max(case when b.[Start Date] is null and b.[End Date] is null then a.[PTID] END) as [Next_PTID]
from  
  [TableA] a 
inner join [TableB] b on a.ConsultantsRefDataIDs = b.ID
where a.TeamPlanningID = 36
group by a.TeamPlanningID

方案2：自关联 #

如果每个TeamPlanningID下固定对应两条目标记录，可通过自关联实现：

select 
  curr.TeamPlanningID,
  curr.PTID as [当前PTID],
  next.PTID as [Next_PTID]
from  
  [TableA] curr
join [TableB] curr_b on curr.ConsultantsRefDataIDs = curr_b.ID
  and curr_b.[Start Date] is not null and curr_b.[End Date] is null
join [TableA] next on curr.TeamPlanningID = next.TeamPlanningID
join [TableB] next_b on next.ConsultantsRefDataIDs = next_b.ID
  and next_b.[Start Date] is null and next_b.[End Date] is null
where curr.TeamPlanningID = 36

最终返回单行数据，格式如下：

内容的提问来源于stack exchange，提问作者user3588404