获取两个表的两列乘法的总和 [英] Get sum of multiplication of two columns of two tables

问题描述

我有这样的产品表

 PRODUCT_ID PACK_SIZE PACK_PRIZE 
 3000 5 2.5 
 3001 5 2.5 
 3002 5 2.5 
 3003 5 2.5 
 

订购表

 order_id client_id 
 75001 1024 
 75002 1033 
 75003 1030 
 

项目表

 ORDER_ID PRODUCT_ID NUMBER_ORDERED 
 75001 3936 2 
 75001 3557 5 
 75001 3012 3 
 75001 3236 4 
 

客户表

<前lang =text> CLIENT_ID LAST_NAME状态
1021 Smith私人
1022 Williams公司
1023 Browne私人
1024 Tinsell corporate

这些是我刚刚添加的样本数据，仅用于显示样本数据。

这里我想要选择总订单最多的前5名客户金额。



我想选择状态为公司且订单数量最多的客户。



换句话说，我想选择总订单金额较高的客户的client_id。



这里我试图像这样实现它。

  WITH  CTE  as （ SELECT  ORDERS.ORDER_ID，PRODUCTS.PACK_PRIZE，PRODUCTS.PACK_SIZE，ITEMS.NUMBER_ORDERED，
 CLIENTS .STATUS，CLIENTS.CLIENT_ID，CLIENTS.FIRST_NAME，CLIENTS.LAST_NAME 
  FROM  ORDERS  INNER  < span class =code-keyword> JOIN  
 ITEMS 
  ON  ORDERS.ORDER_ID = ITEMS.ORDER_ID  INNER   JOIN  
产品
  ON 项目.PRODUCT_ID = PRODUCTS.PRODUCT_ID 
  INNER   JOIN  
客户
 < span class =code-keyword> ON  ORDERS.CLIENT_ID = CLIENTS.CLIENT_ID 
  WHERE  CLIENTS.STATUS = '  corporate' ）
  SELECT  CLIENT_ID，FIRST_NAME，LAST_NAME，ORDER_ID，（（PACK_PRIZE / PACK_SIZE）* NUMBER_ORDERED） AS 总计
  FROM （ SELECT  CTE。* 
  FROM  CTE 
  ORDER   BY  SUM（PACK_PRIZE / PACK_SIZE ）* NUMBER_ORDERED  DESC  
）t 
  WHERE  rownum< =  5 ; 

但在这里我获得了最高订单，因此订单相同客户也参与其中。



我想选择总订单金额最高的前5位客户。



**全部应该选择公司的客户。

然后应该计算每个客户的所有订单的总金额

我想选择具有最高10个值的客户。**

我正在使用oracle 11g。

解决方案

当你在select中使用'ORDER_ID'列时，你会有重复的客户信息。您需要执行以下操作：



1）使用 SUM分析示例

基于clientID



2 ）在此总和列上使用 ROW NUMBER 功能生成您的等级值。



3）将row_num< = 10放在where子句中。



希望它帮助

I have product table like this

PRODUCT_ID  PACK_SIZE   PACK_PRIZE
 3000           5       2.5
 3001           5       2.5
 3002           5       2.5
 3003           5       2.5

Order table

order_id     client_id
75001   1024
75002   1033
75003   1030

ITEMS Table

ORDER_ID    PRODUCT_ID  NUMBER_ORDERED
 75001  3936    2
 75001  3557    5
 75001  3012    3
 75001  3236    4

Client Table

CLIENT_ID   LAST_NAME    STATUS
 1021    Smith          private
 1022    Williams       corporate
 1023    Browne         private
 1024    Tinsell        corporate

These are sample data I just added these just to show sample data.
Here I want to select top 5 clients who is having highest total orders amount.

I want to select the clients whose status is corporate and who are having the highest amount of orders.

In other words i want to select client_id s of clients whose having higher total order amount.

Here I'm trying to achieve it like this.

WITH CTE as ( SELECT ORDERS.ORDER_ID, PRODUCTS.PACK_PRIZE, PRODUCTS.PACK_SIZE,    ITEMS.NUMBER_ORDERED,
      CLIENTS.STATUS,CLIENTS.CLIENT_ID,CLIENTS.FIRST_NAME,CLIENTS.LAST_NAME
       FROM ORDERS INNER JOIN
           ITEMS
           ON ORDERS.ORDER_ID = ITEMS.ORDER_ID INNER JOIN
           PRODUCTS
           ON ITEMS.PRODUCT_ID = PRODUCTS.PRODUCT_ID
           INNER JOIN
           CLIENTS
           ON ORDERS.CLIENT_ID = CLIENTS.CLIENT_ID
           WHERE CLIENTS.STATUS='corporate')
    SELECT CLIENT_ID,FIRST_NAME,LAST_NAME,ORDER_ID,((PACK_PRIZE/PACK_SIZE) * NUMBER_ORDERED)AS Total
    FROM (SELECT CTE.*
      FROM CTE
      ORDER BY SUM(PACK_PRIZE/PACK_SIZE) * NUMBER_ORDERED DESC
     ) t
     WHERE rownum <= 5;

But here I'm getting the highest orders, so orders by same client comes in this too.

I want to select the top 5 customers who is having highest total order amount.

**All the clients who are corporate should be selected.
Then total amount of all the orders of each client should be calculated
and I want to select the clients with highest 10 values.**
I'm using oracle 11g.

解决方案

When you use 'ORDER_ID' column in select then you will have repeated client information. you need to do the following:

1) calculate the sum of the ordered amount using SUM Analytic Example
based on clientID

2) On this sum column use ROW NUMBER function to generate your rank of the values.

3) Put row_num <= 10 in your where clause.

hope it helps.

这篇关于获取两个表的两列乘法的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！