使用新建Participant列过滤SQL查询失败求助

数据库中同一个Request_number对应多行数据（每个请求状态一行，每行有独立的Request_ID），但每个Request_ID的User_ID仅在其中一行有值。我尝试用FIRST_VALUE函数生成Participant列填充User_ID的NULL值，但用该列过滤时出现错误。

原查询语句 #

WITH Grp as
(SELECT req.[Request_ID]
      ,[Request_number]
      ,[User_ID]
  FROM [Data2].[Counter].[Requests_Facts] req
  LEFT OUTER JOIN [Data2].[Counter].[Contributor] cont on req.Request_ID = cont.Request_ID)

  SELECT req.[Request_ID]
      ,[Request_number]
      ,typ.[Description] as 'Request subject'
      ,req.[Date_Created]
      ,req.[Date_Started]   
      ,req.[End_Date]   
      ,req.[Date_Modified]  
      ,[Date_Processed]
      ,loc.[Description] as 'Location'
      ,team.[Description] as 'Team' 
      ,ori.[Description] as 'Origin'    
      ,pri.[Description] as 'Priority'  
      ,comp.[Description] as 'Complexity'   
      ,sts.[Description] as 'Status'    
      ,req.[Language]
      ,sec.[Description] as 'Sector'
      ,FIRST_VALUE(User_ID) OVER(Partition by Request_number, Request_number ORDER BY User_ID DESC) as 'Participant'
      FROM [Data2].[Counter].[Requests_Facts] req
  LEFT OUTER JOIN [Counter].[Contributor] cont on req.Request_ID = cont.Request_ID
  LEFT OUTER JOIN [Counter].[RequestType] typ on req.ID_RequestType = typ.ID_RequestType    
  LEFT OUTER JOIN [Counter].[Location] loc on req.ID_Location = loc.ID_Location 
  LEFT OUTER JOIN [Counter].[Team] team on req.ID_Team = team.ID_Team   
  LEFT OUTER JOIN [Counter].[Origin] ori on req.ID_Origin = ori.ID_Origin   
  LEFT OUTER JOIN [Counter].[Priority] pri on req.ID_Priority = pri.ID_Priority 
  LEFT OUTER JOIN [Counter].[Complexity] comp on req.ID_Complexity = comp.ID_Complexity 
  LEFT OUTER JOIN [Counter].[Statuts] sts on req.ID_Status = sts.ID_Status  
  LEFT OUTER JOIN [Counter].[Sector] sec on req.ID_Sector = sec.ID_Sector
  WHERE [Participant] = '1234'

第一次错误信息 #

Msg 207, Level 16, State 1, Line 36

Invalid column name 'Participant'

尝试修改后的查询片段 #

WHERE FIRST_VALUE(User_ID) OVER (PARTITION BY Request_number, Request_number ORDER BY User_ID DESC) = '1234'

第二次错误信息 #

Msg 4108, Level 15, State 1, Line 36

Windowed functions can only appear in the SELECT or ORDER BY clauses

解决方案 #

SQL的执行顺序中，WHERE子句在SELECT之前执行，所以无法直接在WHERE里使用SELECT中定义的列别名，也不能直接用窗口函数（窗口函数只能在SELECT或ORDER BY中使用）。解决方法是先通过CTE或子查询生成包含Participant列的数据集，再在外部查询中过滤。

修正后的查询：

WITH RequestWithParticipant AS (
    SELECT 
        req.[Request_ID],
        req.[Request_number],
        typ.[Description] AS 'Request subject',
        req.[Date_Created],
        req.[Date_Started],
        req.[End_Date],
        req.[Date_Modified],
        req.[Date_Processed],
        loc.[Description] AS 'Location',
        team.[Description] AS 'Team',
        ori.[Description] AS 'Origin',
        pri.[Description] AS 'Priority',
        comp.[Description] AS 'Complexity',
        sts.[Description] AS 'Status',
        req.[Language],
        sec.[Description] AS 'Sector',
        -- 修正PARTITION BY重复问题，明确指定User_ID来源
        FIRST_VALUE(cont.User_ID) OVER(PARTITION BY req.Request_number ORDER BY cont.User_ID DESC) AS 'Participant'
    FROM [Data2].[Counter].[Requests_Facts] req
    LEFT OUTER JOIN [Counter].[Contributor] cont ON req.Request_ID = cont.Request_ID
    LEFT OUTER JOIN [Counter].[RequestType] typ ON req.ID_RequestType = typ.ID_RequestType
    LEFT OUTER JOIN [Counter].[Location] loc ON req.ID_Location = loc.ID_Location
    LEFT OUTER JOIN [Counter].[Team] team ON req.ID_Team = team.ID_Team
    LEFT OUTER JOIN [Counter].[Origin] ori ON req.ID_Origin = ori.ID_Origin
    LEFT OUTER JOIN [Counter].[Priority] pri ON req.ID_Priority = pri.ID_Priority
    LEFT OUTER JOIN [Counter].[Complexity] comp ON req.ID_Complexity = comp.ID_Complexity
    LEFT OUTER JOIN [Counter].[Statuts] sts ON req.ID_Status = sts.ID_Status
    LEFT OUTER JOIN [Counter].[Sector] sec ON req.ID_Sector = sec.ID_Sector
)
SELECT *
FROM RequestWithParticipant
WHERE [Participant] = '1234';

补充说明 #

1. 原查询中定义的Grp CTE未被使用，已删除；
2. 窗口函数的PARTITION BY重复写了Request_number，已修正为只写一次；
3. 明确指定cont.User_ID，避免表间字段名冲突；
4. 先在CTE中计算出Participant列，外部查询即可正常使用该列进行过滤。

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