合并两个查询Oracle SQL

Kelsie 发表于 Dev

凯尔西

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

select clientid, sum(fee) as "Total Spent"
from   bookings
group by clientid;

select l.clientid, sum(m.price * l.quantity) as "Total Spent"
from   lineitems l
       join merchandise m on m.merchid = l.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  |
+----------+-------+------------+----------+-----------+---------+------------+

LineItems表格：

+----------+-------+------------+----------+-----------+---------+-----+
| ClientID | Tour  | EventMonth | EventDay | EventYear | MerchId | Qty |
+----------+-------+------------+----------+-----------+---------+-----+
|       12 | South | Feb        |       20 |      2016 |      20 |   1 |
+----------+-------+------------+----------+-----------+---------+-----+

商品表：

+---------+----------+------------+-------+
| MerchID | Category | ProdName   | Price | 
+---------+----------+------------+-------+
|      20 | A        | Highway    |    85 |    
+---------+----------+------------+-------+

任何帮助将不胜感激

威廉·罗伯逊

这基本上与Gordon的答案相同，但是您的示例数据是内联的，并具有总计：

-- Your sample data:
with bookings (clientid, tour, eventmonth, eventday, eventyear, payment, datebooked ) as
     ( select 12, 'South', 'Feb', 20, 2016, 225, date '2016-02-19' from dual union all
       select 12, 'West',  'Mar', 5,  2016, 225, date '2016-03-03' from dual union all
       select  2, 'West',  'Mar', 5,  2016, 225, date '2016-03-03' from dual union all
       select  2, 'West',  'Mar', 6,  2017, 225, date '2016-03-03' from dual union all
       select  2, 'West',  'Mar', 7,  2018, 225, date '2016-03-03' from dual )
   , lineitems (clientid, tour, eventmonth, eventday, eventyear, merchid, quantity) as
     ( select 12, 'South', 'Feb', 20, 2016, 20, 1 from dual )
   , merchandise (merchid, category, prodname, price) as
     ( select 20, 'A', 'Highway', 85 from dual )
--
-- Actual query starts here
--
select b.clientid
     , bookings_total
     , coalesce(merchandise_total,0) as merchandise_total
     , bookings_total + coalesce(merchandise_total,0) as grand_total
from   ( select clientid, sum(payment) as bookings_total
         from   bookings
         group by clientid ) b
       left join
        ( select l.clientid, sum(l.quantity * m.price) as merchandise_total
          from   lineitems l
                 join merchandise m on m.merchid = l.merchid
          group by clientid ) lm
        on  lm.clientid = b.clientid;


CLIENTID BOOKINGS_TOTAL MERCHANDISE_TOTAL GRAND_TOTAL
-------- -------------- ----------------- -----------
      12            450                85         535
       2            675                 0         675

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。