我有这样的产品表
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.5Order table
order_id client_id 75001 1024 75002 1033 75003 1030ITEMS Table
ORDER_ID PRODUCT_ID NUMBER_ORDERED 75001 3936 2 75001 3557 5 75001 3012 3 75001 3236 4Client Table
CLIENT_ID LAST_NAME STATUS 1021 Smith private 1022 Williams corporate 1023 Browne private 1024 Tinsell corporateThese 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.更多推荐
获取两个表的两列乘法的总和
发布评论