合并两个查询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);
    

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