mysql - 获取每个订单的成本和时间

Question

我有以下六个表:

员工

PK: employeeID;     
FK: empBranch references BRANCH;
FK: empSupervisor references EMPLOYEE

客户

PK: customerID

订单

PK: orderNumber
FK: customerID references CUSTOMER; 
FK: salesPerson references EMPLOYEE

产品

PK: productCode

产品线

PK: orderNumber + prodCode
FK: orderNumber references ORDERS;
FK: prodCode references PRODUCT

指导

PK: orderNumber + instType
FK: orderNumber references ORDERS; 
FK: instType refesnrences INSTALLATION

我正在做一个项目，但陷入了困境。谁能帮我列出每个订单的订单号、订单日期、销售人员的员工 ID、产品总金额和安装总金额。产品总金额是单价乘以订购产品数量的总和。安装总金额是小时数乘以安装类型的计费率的总和。两个小时以来我一直在尝试查询它。请帮忙

答案中提到的上述部分的解决方案

Select o.ORDERNUMBER, ORDERDATE, SALESPERSON, 
SUM (PRICE * QUANTITY) as TOTALPRODUCTAMOUNT, 
SUM (HOURS * RATE) as TOTALINSTALLCOST 
from ORDERS o 
join PRODLINE pl on pl. ORDERNUMBER = o. ORDERNUMBER
join PRODUCT p on p.PRODUCTCODE = pl. PRODUCTCODE
join INSTLINE il on il. ORDERNUMBER = o. ORDERNUMBER
join INSTALLATION i on i.INSTALLTYPE = il.INSTTYPE
group by o. ORDERNUMBER, ORDERDATE, SALESPERSON;

编辑:假设订单的总金额由 TOTALPRODUCTAMOUNT 和 TOTALINSTALLCOST 的总和给出。一个分支产生的revenue_generated是在该分支工作的销售人员的所有订单总额的总和。因此，我们需要计算出分支编号、分支名称、revenue_target，以及每个未达到 revenue_target 的分支的 revenue_generated。

我们可以假设 Branch 表如下:

Answer 1

select o.orderNumber,
       ordDate,
       salesPerson,
       SUM(price * quantity) as totalProductAmount,
       SUM(hours * billingRate) as totalInstallationAmount
from ORDERS o
join PRODLINE pl on pl.orderNumber = o.orderNumber
join PRODUCT p on p.productCode = pl.prodCode
join INSTLINE il on il.orderNumber = o.orderNumber
join INSTALLATION i on i.installationType = il.instType
group by o.orderNumber, ordDate, salesPerson

编辑第二个问题的答案。

select branchNumber,
       branchName,
       revenueTarget,
       SUM((price * quantity) + (hours * billingRate)) as revenueAttained
from BRANCH b
join EMPLOYEE e on e.empBranch = b.branchNumber
join ORDERS o on o.salesPerson = e.employeeID
join PRODLINE pl on pl.orderNumber = o.orderNumber
join PRODUCT p on p.productCode = pl.prodCode
join INSTLINE il on il.orderNumber = o.orderNumber
join INSTALLATION i on i.installationType = il.instType
group by branchNumber, branchName, revenueTarget