合并两个查询Oracle SQL

我正在尝试将我的两个查询完美地结合在一起。但是我一直坚持尝试使它们团结起来,共同发挥出预期的效果。 这两个查询是:

SELECT CLIENTID, SUM(FEE) AS "Total Spent"
FROM BOOKINGS2017
GROUP BY CLIENTID

SELECT L.CLIENTID, SUM(M.PRICE * L.QUANTITY) AS "Total Spent"
FROM LINEITEM2017 L
JOIN MERCHANDISE2017 M
ON L.MERCHID = M.MERCHID
GROUP BY L.CLIENTID

因此,最终目标是合并每个客户在预订和购买上花费的金额。 即客户ID 12的预订支出为450美元,产品支出为85美元;因此总计为535美元。

数据集是这样的:

预订表:

ClientId,Tour,EventMonth,EventDay,EventYear,Payment,DateBooked
12,South,Feb,20,2016,225,19/02/2016
12,West,Mar,5,2016,225,3/03/2016

LineItem表格:

CustId,Tour,EventMonth,EventDay,EventYear,ProdId,Qty
12,South,Feb,20,2016,20,1

任何帮助将不胜感激

评论
卟~怕
卟~怕

You can use join:

SELECT *
FROM (SELECT CLIENTID, SUM(FEE) AS TotalSpent
      FROM BOOKINGS2017
      GROUP BY CLIENTID
     ) b JOIN
     (SELECT L.CLIENTID, SUM(M.PRICE * L.QUANTITY) AS TotalSpent
      FROM LINEITEM2017 L JOIN
           MERCHANDISE2017 M
           ON L.MERCHID = M.MERCHID
      GROUP BY L.CLIENTID
    ) m
    USING (CLIENTID);

如果表具有不同的客户端集,则可能需要外部联接。

点赞
评论