扩展SQL函数的功能

Question

我有这样一个函数，返回在特定课程上工作的人员的成本，该课程可以有一个或多个实验室助理和一个或多个人员个人，只要只有一名工作人员和一名助理在具体课程上工作，我想让它对任何数量的工作人员和助手有效，我如何才能扩展这个功能来获得这种功能呢？

ALTER FUNCTION [dbo].[getCourseCost]
(
    @CourseCode varchar(50),
    @Year nchar(10),
    @Period nchar(10)
)
RETURNS int
AS
BEGIN
DECLARE @SSN_staff varchar(50)
DECLARE @Salary_staff int
DECLARE @Hours_staff int
DECLARE @Cost_staff int
DECLARE @Cost_labass int

SELECT @SSN_staff = SSN FROM Attended_By WHERE Period = @Period AND Year = @Year AND CourseCode = @CourseCode
SELECT @Salary_staff = Salary FROM Staff WHERE SSN = @SSN_staff
SELECT @Hours_staff = Hours FROM Attended_By WHERE Period = @Period AND Year = @Year AND CourseCode = @CourseCode
SELECT @Cost_labass = (Hours * Salary) FROM Labass WHERE Period = @Period AND Year = @Year AND CourseCode = @CourseCode

SET @Cost_staff = @Salary_staff * @Hours_staff
IF @Cost_staff is NULL
BEGIN
SET @Cost_staff = 0
END

IF @Cost_labass is NULL
BEGIN
SET @Cost_labass = 0
END

RETURN @Cost_staff + @Cost_labass
END

Answer 1

我不得不从代码中对您的表做了一些假设，因为我并不明确地确定它们是什么，但是假设Attended_By.Hours是课程中一名工作人员参加的小时数，并且Staff.Salary规定了工作人员每小时的成本，下面的查询应该为课程中的所有工作人员提供一个单一的成本：

SELECT  @Cost_Staff = SUM(A.Hours * S.Salary)
  FROM  Attended_By A
    INNER JOIN Staff S ON S.SSN = A.SSN
  WHERE (Period = @Period AND
        Year = @Year AND
        CourseCode = @CourseCode)

这将使每名工作人员的所有时数乘以其各自的薪金。使用上面的内容，您可以为@SSN_staff、@Salary_staff和@Hours_staff免除所有三个@Salary_staff。

我认为，如果您使用SUM函数，用于实验室助理的查询应该已经返回总数：

SELECT  @Cost_labass = SUM(Hours * Salary)
  FROM  Labass
  WHERE (Period = @Period AND
        Year = @Year AND
        CourseCode = @CourseCode)