c# - 在SQL中为外键替换参数

SELECT
    StaffID,
    SUM(Treatment.TreatmentPrice) AS TotalStaffRevenue,
    SUM(Treatment.TreatmentPrice) * ? AS Commission
FROM
    Treatment
INNER JOIN
    Appointment ON Appointment.TreatmentID = Treatment.TreatmentID
WHERE
    AppointmentDate >= '2018/05/11'
    AND AppointmentDate <= '2018/05/12'
GROUP BY
    staffid WITH ROLLUP

CREATE TABLE Staff (
StaffID int IDENTITY NOT NULL,
Forename varchar(20) NOT NULL,
Surname varchar(20) NOT NULL,
MobileNumber varchar(11) NOT NULL,
EmailAddress varchar(20) NULL,
PostCode varchar(8) NULL,
HouseNumber varchar(4) NULL,
Commission decimal(6,2) NOT NULL,
PRIMARY KEY(StaffID)
);

GO

CREATE TABLE Treatment (
TreatmentID int identity NOT NULL,
TreatmentName varchar(20)  NOT NULL,
TreatmentPrice money NOT NULL,
CategoryID int NOT NULL,
TreatmentDescription varchar(40) NOT NULL,
PRIMARY KEY(TreatmentID, CategoryID)
);

GO

CREATE TABLE Appointment (
AppointmentID int IDENTITY NOT NULL,
StaffID int NOT NULL,
TreatmentID int NOT NULL,
CustomerID int NOT NULL,
AppointmentDate date NOT NULL,
AppointmentTime time(0) NOT NULL,
PRIMARY KEY (AppointmentID, StaffID, CustomerID, TreatmentID)
);

我希望能够将TreatmentPrice的总和乘以Staff.Commission。我已经尝试过与Staff进行INNER JOINing，但是仍然无法正常工作，我该如何在前端（c＃）中实现此功能，我将每个循环都使用a，然后填充一个数据网格。

最佳答案

在没有任何查询中的Staff表的情况下，如何获得Staff.Commission？
尝试

SELECT
    Staff.StaffID,
    SUM(Treatment.TreatmentPrice) AS TotalStaffRevenue,
    SUM(Treatment.TreatmentPrice)*Commission AS Commission
FROM
    Treatment
INNER JOIN
    Appointment ON Appointment.TreatmentID = Treatment.TreatmentID
    inner join Staff on Appointment.StaffID=Staff.StaffID
WHERE
    AppointmentDate >= '2018/05/11'
    AND AppointmentDate <= '2018/05/12'
GROUP BY
    Staff.StaffID,Commission WITH ROLLUP

关于c# - 在SQL中为外键替换参数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50280335/