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

Question

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#) 中实现它，我会为每个循环使用一个 for each 循环，然后填充一个数据网格。

Answer 1

在您的查询中没有任何连接到 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