获取两个表的两列乘法的总和

编程入门 行业动态 更新时间:2024-10-17 07:30:12
本文介绍了获取两个表的两列乘法的总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述

我有这样的产品表

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

客户表 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 JOIN ITEMS ON ORDERS.ORDER_ID = ITEMS.ORDER_ID INNER JOIN 产品 ON 项目.PRODUCT_ID = PRODUCTS.PRODUCT_ID INNER JOIN 客户 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.

更多推荐

获取两个表的两列乘法的总和

本文发布于:2023-11-21 23:34:16,感谢您对本站的认可!
本文链接:https://www.elefans.com/category/jswz/34/1615110.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:乘法   总和   两个

发布评论

评论列表 (有 0 条评论)
草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍!