两列值乘以日期范围的 SQL SUM

Question

我需要回答的问题是“为一位名叫‘John Smith’的患者提供吗啡的总费用是多少？”

在我的数据库架构中，我有以下架构：

Patient (patientNo, patName, patAddr, DOB)
Ward (wardNo, wardName, wardType, noOfBeds)
Contains (patientNo, wardNo, admissionDate)
Drug (drugNo, drugName, costPerUnit)
Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate)

我在这里为上述内容创建了一个示例架构：https://www.db-fiddle.com/f/wecC7cjtryKMErqPskNr41/1

如何构造查询以将 costPerUnit*unitsPerDay 乘以 startDate 和 finishDate 之间的天数，而无需手动计算天数？

目前我有：

SELECT SUM r.unitsPerDay*d.costPerUnit BETWEEN '2022-05-25' AND '2022-06-25' AS TotalCost, p.patName, d.drugName
FROM Prescribed r, Drug d, Patient p
WHERE p.patientNo=r.patientNo AND d.drugNo=r.drugNo AND r.drugNo=1001;

来自以下内容：

insert into Drug (drugNo, drugName, costPerUnit) values (1001, 'Morphine', '25.00');
insert into Patient (patientNo, patName, patAddr, DOB) values (101, 'John Smith', '123 Street', DATE '1990-01-01');
insert into Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) values (101, 1001, 4, DATE '2022-05-25', DATE '2022-06-25');

Answer 1

尝试这个查询：

SELECT SUM (r.unitsPerDay*d.costPerUnit*(finishDate-startDate)) AS TotalCost, p.patName, d.drugName
FROM Prescribed r, Drug d, Patient p
WHERE p.patientNo=r.patientNo AND d.drugNo=r.drugNo AND r.drugNo=1001;