CROSS APPLY应用实例

 

--功能说明:统计每个人的平均分数,新字段[AVG_Score]根据PersonID链接到原表[tbiz_AssScore]上

SELECT [ID]
  ,[ProjectID]
  ,[PersonID]
  ,[PersonName]
  ,[RelationID]
  ,[RelationName]
  ,[SortNum]
  ,[Score]
  ,X.AVG_Score
FROM [tbiz_AssScore] A
CROSS APPLY
(
    SELECT  
    AVG([Score]) AVG_Score
    FROM    [tbiz_AssScore] M
    WHERE A.PersonID=M.PersonID
    GROUP BY M.PersonID
) X