如何调用select语句中的函数并计算合计

Question

我有如下表格：

Employees : EmployeeId, EmpName , DepartmentId
Transaction: TransactionId, EmployeeId

我有一个查询，返回员工列表：

select * from Employees e
inner join Department d on e.DepartmentId = d.Id
--some other joins
where d.DepartmentId in (@departmentIds)
--other filters

我有一个"；标量值"；函数名为"；CCD_ 1"；它计算每笔交易的雇员佣金；佣金"；针对该特定交易。

现在我想对事务表进行左联接，并调用"；CCD_ 2"；在每次交易中；总佣金"；最终结果如下：

最终结果集：

EmployeeId  EmpName   DepartmentId   Total Commission
1           John      100             1000
2           Frieda    200             2000

但我有点困惑于调用该函数，然后为每个员工传递多个交易ID并计算总佣金。

有人能帮我做这个吗？

功能如下：

CREATE FUNCTION [dbo].[GetEmployeeCommissionByTransactionId]
(
@transactionId INT
)
RETURNS Decimal(18,0)
AS
BEGIN
DECLARE @Commission DECIMAL;
-----Logic to calculate commission---------
RETURN @Commission;
End

Answer 1

要使用您创建的函数计算多笔交易的佣金，您必须sum所有相关交易的函数结果，然后将数据分组以获得所需列。

select e.EmployeeId, e.EmpName, d.DepartmentId
, sum(dbo.GetEmployeeCommissionByTransactionId(t.id)) Commission
from Employees e
inner join Department d on e.DepartmentId = d.Id
left join [Transaction] t on t.EmployeeId = e.Id
where d.DepartmentId in (@departmentIds)
group by e.EmployeeId, e.EmpName, d.DepartmentId;

然而，正如评论中所指出的，一种更具性能的方法是内联表值函数，它接受EmployeeId而不是TransactionId。

Answer 2

我不确定如何加入事务表，但不确定如何使用外部应用程序。

SELECT e.Id AS EmployeeId, e.EmpName, e.DepartmentId, tx.TotalCommission
FROM Employees e 
INNER JOIN Department d ON e.DepartmentId = d.Id 
OUTER APPLY (
SELECT [dbo].[GetEmployeeCommissionByTransactionId](t.TransactionId) AS TotalComission 
FROM Transactions t WHERE t.EmployeeId = e.Id) tx