tsql - TSQL与计数和总和交叉应用

Question

我刚刚发现了CROSS APPLY运算符，并给人以印象，它在处理派生列进行计算时非常有用。

因此，我尝试了以下方法:

SELECT leadYear,TotalLeadsCalled,SuccessLeadsCalled,SuccessLeadsPercent
FROM dbo.tblBinOutboundCallActivity 
CROSS APPLY(VALUES (YEAR(leadactivitydate))) AS a1(leadyear) 
CROSS APPLY(VALUES (COUNT(leadStatusID))) AS a2(TotalLeadsCalled) 
CROSS APPLY(VALUES (COUNT(CASE WHEN leadStatusID = 2 THEN 1 ELSE NULL END))) AS a3(SuccessLeadsCalled) 
CROSS APPLY(VALUES (SUM((SuccessLeadsCalled/TotalLeadsCalled)*100))) AS a4(SuccessLeadsPercent) 
GROUP BY leadYear

但是我收到以下错误:

Aggregates on the right side of an APPLY cannot reference columns from the left side.

我不太了解该错误，尤其是在第一个交叉应用实际上是孤立运行的情况下。我只是对这个概念完全错误了吗？

Answer 1

在线路级别交叉应用作品。

“对于左侧的每一行，我们在右侧应用一个函数”。

您的汇总在每行基础上没有意义。