我要招人

去企业版

首页 > 试题广场 >

返回每个顾客不同订单的总金额

[编程题]返回每个顾客不同订单的总金额

热度指数：165619 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。

OrderItems表代表订单信息，OrderItems表有订单号：order_num和商品售出价格：item_price、商品数量：quantity。

Orders表订单号：order_num、顾客id：cust_id

【问题】

编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总金额，将结果按金额从大到小排序。

【示例结果】返回顾客id cust_id和total_order下单总额

【示例解析】cust2在Orders里面的订单a0013，a0013的售出价格是2售出数量是1121，总额是2242，最后返回cust2的支付总额是2242。

示例1

输入

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	item_price INT(16) NOT NULL COMMENT '售出价格',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a0001','cust10'),('a0003','cust1'),('a0013','cust2');

输出

cust_id|total_ordered
cust2|2242
cust10|1050
cust1|104

算法知识视频讲解

吾亦悠然归山

两种写法：

#1
SELECT cust_id,
       (SELECT SUM(quantity * item_price)
        FROM OrderItems
        WHERE OrderItems.order_num = Orders.order_num) AS total_ordered
FROM Orders
ORDER BY total_ordered DESC;

#2
SELECT cust_id,
       SUM(quantity * item_price) AS total_ordered
FROM Orders, OrderItems
WHERE Orders.order_num = OrderItems.order_num
GROUP BY cust_id
ORDER BY total_ordered DESC;

发表于 2022-03-15 17:17:18 回复(18)

Mysql

老李你快开炮

结论：这道题出的有问题！！

这是Orders这张表：

cust1是有两行数据的，但是出题人的生成这张表记录的sql是这样子的：

少了一行cust1的数据！！

然后示例结果给的也有问题：

请问这个示例结果的cust_id字段为什么没去重？

发表于 2022-03-22 11:16:58 回复(12)

Mysql

半路出家的蛋头

题目给的不严谨，准确的说是测试设计的不严谨，题目里面有两个order_num a0002和a0003对应着cust_id 为cust1的数据,但是测试用例设计的时候没体现这一条重复数据。所以前面好多解答都只做一次分组就可以通过测试的方法不严谨。

order_num	cust_id
a0001	cust10
a0002	cust1
a0003	cust1
a0013	cust2

我认为比较严谨的写法应该是：

select
  cust_id,
  sum(q_total_ordered) as total_ordered
from
  (
    select
      order_num,
      sum(item_price * quantity) as q_total_ordered
    from
      OrderItems
    group by
      order_num
  ) as temp,
  Orders
where
  temp.order_num = Orders.order_num
group by
  cust_id
order by
  total_ordered desc

发表于 2022-07-08 15:20:28 回复(8)

Mysql

牛客154019670号

select b.cust_id,sum(a.item_price*a.quantity) as total_ordered
from OrderItems a join Orders b on a.order_num = b.order_num
group by b.cust_id
order by total_ordered desc

发表于 2022-03-03 13:51:50 回复(2)

Mysql

牛客160200733号

这个题目真心的奇葩了，题目里的Orders表中order_num字段下有a0002这个值，但是在后台输入的数据中没有，结果造成以OrderItems作为主表时出现cust_id字段出现NULL，答案错误，只能以Orders表作为主表或者内连接。

INSERT `Orders` VALUES ('a0001','cust10'),('a0003','cust1'),('a0013','cust2');
#没有'a0002'这个值，奇葩吧

发表于 2022-03-16 00:03:04 回复(8)

PyaneT

select cust_id, sum(quantity*item_price) total_ordered 
from OrderItems a, Orders b 
where a.order_num = b.order_num
group by cust_id
order by sum(item_price*quantity) desc

发表于 2022-03-04 10:20:26 回复(4)

冷凡社长

select t.cust_id,SUM(quantity*item_price) as total_ordered
from Orders t inner join OrderItems t1 on t.order_num=t1.order_num
group by cust_id
ORDER BY total_ordered DESC

仍然是表链接更简单易理解。

SELECT cust_id,
       (SELECT SUM(quantity * item_price)
        FROM OrderItems
        WHERE OrderItems.order_num = Orders.order_num) AS total_ordered
FROM Orders
ORDER BY total_ordered DESC;

关联子查询就需要理解他的运算逻辑了，认知难度更高一点，主要是实际中用的上，很难记得牢固

发表于 2022-08-15 19:46:45 回复(7)

员序程马黑

select cust_id,sum(item_price*quantity) as total_ordered from OrderItems
join 
Orders
using(order_num)
group by cust_id
order by total_ordered desc

发表于 2022-04-27 16:22:05 回复(0)

Mysql

牛客761947127号

子查询方法

select cust_id, total_ordered
from Orders as a, (
    select order_num, sum(item_price*quantity) as total_ordered
    from OrderItems
    group by order_num) as b
where a.order_num = b.order_num
order by total_ordered desc

发表于 2022-09-02 15:30:34 回复(0)

Mysql

小梁快变强

## 关联表做法
select b.cust_id,a.total_order
from Orders b
join (select order_num, sum(item_price * quantity) as total_order
from OrderItems
group by order_num) a
on b.order_num = a.order_num
order by a.total_order desc;

发表于 2022-04-08 16:24:41 回复(0)

Mysql

牛客196016039号

select cust_id,b.total_ordered
from Orders a
left join
(select order_num,sum(item_price*quantity)as total_ordered
from OrderItems
group by order_num) b
on a.order_num=b.order_num
order by total_ordered desc

发表于 2022-03-09 20:31:56 回复(1)

Mysql

福行天下

select
    os.cust_id,
    (select sum(item_price*quantity) from OrderItems where order_num=os.order_num) as total_ordered 
from Orders os

order by 2 desc

发表于 2022-03-04 17:42:02 回复(3)

Mysql

sqlsquare

发表于 2022-03-02 16:57:28 回复(0)

Mysql

潘伟靖

子查询，正确解法：

select cust_id, total_ordered
from (select order_num, sum(item_price * quantity) as total_ordered
     from OrderItems
     group by order_num) t, Orders od
where t.order_num = od.order_num
order by total_ordered desc

发表于 2022-08-06 11:18:04 回复(2)

Mysql

Stzolo

select cust_id, sum(item_price * quantity) as total_ordered
from Orders a 
join OrderItems b 
on a.order_num = b.order_num
group by cust_id
order by total_ordered desc

发表于 2022-07-17 11:41:21 回复(1)

风止于秋水我止于你

select cust_id,
sum(item_price*quantity) total_ordered
from Orders, OrderItems
where Orders.order_num = OrderItems.order_num
group by cust_id
ORDER BY total_ordered DESC;

发表于 2022-07-13 22:48:32 回复(0)

牛客812008019号

select cust _id，sum（item_price*quantity ）as total_ordered from orderitems a join orders b on a.order_num=b.order_num group by cyst_id，order_num order by total_ordered desc 最后给的示例结果中最后104应该对应的是cust1，另最后结果没有去重，说明不是单纯的以cust_id来分组的，有不对之处，烦请指正～

发表于 2022-04-22 14:08:47 回复(0)

Mysql

牛客927943920号

select cust_id,sum(sum_ordered) as total_ordered
from (
select order_num,sum(item_price*quantity) as sum_ordered
from OrderItems
group by order_num
) t1 join Orders using(order_num)
group by cust_id
order by total_ordered desc

发表于 2022-03-02 17:31:45 回复(1)

Mysql

牛客728731926号

SELECT 
    o.cust_id,
    sum(oi.item_price * oi.quantity) AS total_ordered
FROM Orders o 
JOIN OrderItems oi 
    USING (order_num)
GROUP BY o.cust_id
ORDER BY total_ordered DESC

发表于 2022-03-30 17:01:11 回复(0)

Mysql