sql

xe55xuns 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(218)

我正在尝试将两个单独运行得非常好的查询组合起来。但我一直在努力让他们团结起来，共同工作，并取得预期的成果。这两个查询是：

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;

因此，最终目标是将每个客户用于预订和购买的金额结合起来。i、 e.客户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  |
+----------+-------+------------+----------+-----------+---------+------------+

行项目表：

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

商品表：

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

任何帮助都将不胜感激

sql oracle

来源：https://stackoverflow.com/questions/61980795/combining-two-queries-oracle-sql

2条答案

按热度按时间

8yoxcaq71#

这与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

赞(0）回复(0）举报 2021-07-26

ubof19bj2#

你可以用 join :

SELECT b.*, m.*, b.totalspent + c.totalspent
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);

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

赞(0）回复(0）举报 2021-07-26

我来回答

sql

2条答案

相关问题

热门标签

最新问答