首页 > 试题广场 >

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

[编程题]返回每个顾客不同订单的总金额
  • 热度指数：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
算法知识视频讲解
添加回答
Mysql
牛客501613867号头像 牛客501613867号
select
    O.cust_id,
    S.total_ordered
from
    Orders as O,
    (
        select
            order_num,
            sum(item_price * quantity) as total_ordered
        from
            OrderItems
        group by
            order_num
    ) as S
where
    O.order_num = (S.order_num)
order by
    total_ordered desc;

发表于 2025-12-18 20:57:10 回复(0)
Mysql
牛客783199198号头像 牛客783199198号
怎么改

发表于 2025-11-26 15:41:13 回复(0)
Mysql
能干的海豚就要上岸了头像 能干的海豚就要上岸了
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
发表于 2025-10-16 18:13:05 回复(0)
Mysql
葱白白头像 葱白白 
select cust_id,
sum(item_price*quantity) as total_ordered
from OrderItems a
left join Orders b on a.order_num = b.order_num
group by cust_id
having cust_id is not null
order by total_ordered desc

发表于 2025-08-13 23:47:08 回复(0)
Mysql
lilyzl头像 lilyzl 
select
    o.cust_id,
    sum(i.item_price * i.quantity) as total_ordered
from
    OrderItems i,
    Orders o
where
    i.order_num = o.order_num
group by
    cust_id
order by
    total_ordered DESC;

发表于 2025-08-12 19:07:59 回复(0)
Mysql
除却巫山是小婷头像 除却巫山是小婷
SELECT
    O.cust_id,
    (SELECT SUM(OI.item_price * OI.quantity)
     FROM OrderItems OI
     WHERE O.order_num = OI.order_num) AS total_ordered
FROM
    Orders O
ORDER BY
    total_ordered DESC;
照着课后习题答案写都是错的，后来让ai给改了下才通过
发表于 2025-06-17 16:50:54 回复(1)
Mysql
散步的鱼期待小鱼干头像 散步的鱼期待小鱼干
select cust_id,
sum(item_price*quantity) as total_ordered
from Orders o
left join OrderItems oi
on oi. order_num=o.order_num
group by cust_id
order by total_ordered Desc;
发表于 2025-04-25 10:59:59 回复(0)
Mysql
牛客521682269号头像 牛客521682269号
select cust_id,sum(item_price*quantity) total_ordered
from OrderItems join Orders on OrderItems.order_num=Orders.order_num
group by cust_id
order by total_ordered desc

发表于 2025-03-27 16:29:53 回复(0)
Mysql
牛客1008_头像 牛客1008_
#sum()+group by=分组求和
select *
from
    (
    select
    t2.cust_id,
    sum(t1.item_price*t1.quantity) as total_ordered
    from OrderItems t1
    right join
    Orders t2
    on t1.order_num=t2.order_num
    group by t2.cust_id
    ) as t12
order by total_ordered desc
;
发表于 2025-03-05 13:28:54 回复(0)
Mysql
pep_头像 pep_ 
select
    o.cust_id,
    sum(oi.quantity * oi.item_price) as total_ordered
from
    OrderItems oi
    join Orders o on oi.order_num = o.order_num
group by
    o.cust_id
order by
    total_ordered desc;

发表于 2025-02-18 21:11:14 回复(0)
Mysql
牛客831349683号头像 牛客831349683号
select
    cust_id,
    total_ordered
from
    (select
        cust_id,sum(item_price*quantity) as total_ordered
    from
        OrderItems join Orders using(order_num)
    group by
        cust_id
    ) as t
order by
    total_ordered desc
发表于 2025-02-17 12:52:02 回复(0)
Mysql
Yaki呀头像 Yaki呀 
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;

发表于 2024-11-19 16:28:38 回复(0)
Mysql
在抱佛脚的柯基头像 在抱佛脚的柯基
这种写法也可以
SELECT `cust_id`, SUM(`item_price`*`quantity`) AS `total_ordered` FROM `OrderItems` p
INNER JOIN `Orders` q ON p.`order_num`=q.`order_num`
GROUP BY `cust_id`
ORDER BY `total_ordered` DESC

发表于 2024-11-17 23:54:23 回复(0)
Mysql
垫底辣片头像 垫底辣片 
select o.cust_id, oi.total_ordered
from Orders o
Join (
    select order_num, 
    sum(item_price * quantity) as total_ordered
    from OrderItems
    Group by order_num
) oi using(order_num)
order by total_ordered desc
   一些自己的想法，根据题目要求写的。原题应该是出错了，具体的输出结果可以看它给的代码
发表于 2024-07-19 20:29:39 回复(0)
Mysql
牛客920606582号头像 牛客920606582号 
select
    cust_id,
    total_ordered
from
    Orders
    inner join (
        select
            order_num,
            sum(item_price * quantity) as total_ordered
        from
            OrderItems
        group by
            order_num
    ) a using (order_num)
order by
    total_ordered desc;

发表于 2024-07-16 09:29:50 回复(0)
Mysql
sleeping💤头像 sleeping💤
SELECT cust_id,total_ordered
FROM Orders O,(SELECT order_num,SUM (item_price * quantity) AS total_ordered
                FROM OrderItems
                GROUP BY order_num
                ) AS t
WHERE t.order_num = O.order_num
ORDER BY total_ordered DESC;
这样写为什么会报错显示程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"
哪里有错误呀
编辑于 2024-04-23 22:18:38 回复(0)
Mysql
矫健的小刺猬在看牛客头像 矫健的小刺猬在看牛客
第一种方法：表连接 
select distinct cust_id,sum(item_price*quantity) total_ordered
from OrderItems oi join Orders o on oi.order_num=o.order_num
group by cust_id
order by total_ordered desc
第二种方法：子查询 
select cust_id,
    (select sum(item_price*quantity)
    from OrderItems oi
    where 
    oi.order_num=o.order_num
    ) as total_ordered
from Orders o
order by total_ordered desc
第二种方法不用进行分组是因为在子查询是先在Ordersitem表中的所有数据使用一个过滤条件：拿出这个表里面订单号和第二个表订单号相等的所有金额与数据对应相乘，把它理解成右连接查询就通了。

编辑于 2024-03-22 21:05:03 回复(0)
Mysql
牛客430066965号头像 牛客430066965号
订单表中 a0002并没有对应cust 01
SELECT
    cust_id,oi.total_ordered
from (
        select
        order_num,sum(item_price * quantity) as total_ordered
        from
            OrderItems
        group by
            order_num
    ) oi
LEFT join Orders O
ON  oi.order_num = O.order_num
order by total_ordered desc
发表于 2024-02-06 16:07:53 回复(0)
Mysql
想养修勾的外卷侠一定要上岸头像 想养修勾的外卷侠一定要上岸
求大佬看一看我这哪里错了 
select
    o.cust_id as cust_id,
    oi.qi as total_ordered
from
    (
        select
            order_num,
            sum(orderitems.quantity * orderitems.item_price) qi
        from
            OrderItems
        group by
            order_num
    ) as oi
        join Orders o on oi.order_num = o.order_num
order by
    total_ordered desc;


发表于 2023-11-11 20:32:45 回复(0)
Mysql
布拉拉la头像 布拉拉la 
select
    o.cust_id,
    b.total_ordered
from
    Orders as o, (
        select
            oi.order_num,sum(oi.item_price * oi.quantity) as total_ordered
        from
            OrderItems as oi
        group by
            oi.order_num
    ) as b
where o.order_num=b.order_num
order by b.total_ordered desc

发表于 2023-10-02 20:55:08 回复(0)
提交观点

问题信息

难度：
185条回答 2659浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号