首页 > 试题广场 >

返回顾客名称和相关订单号以及每个订单的总价

[编程题]返回顾客名称和相关订单号以及每个订单的总价

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

Customers 表有字段，顾客名称：cust_name、顾客id：cust_id

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

Orders订单信息表，含有字段，订单号：order_num、顾客id：cust_id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

OrderItems表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price

order_num	quantity	item_price
a1	1000	10
a2	200	10
a3	10	15
a4	25	50
a5	15	25
a7	7	7

【问题】

除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

【示例结果】返回顾客名称 cust_name、订单号order_num、订单总额OrderTotal

cust_name	order_num	OrderTotal
an	a5	375
andy	a1	10000
ben	a2	2000
hex	a7	49
tom	a4	1250
tony	a3	150

【示例解析】

例如顾客名称cust_name为an的顾客的订单a5的订单总额为quantity*item_price = 15 * 25 = 375，最后以cust_name和order_num来进行升序排序。

示例1

输入

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 ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  quantity INT(16) NOT NULL COMMENT '商品数量',
  item_price INT(16) NOT NULL COMMENT '商品价格'
);
INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);

输出

cust_name|order_num|OrderTotal
an|a5|375
andy|a1|10000
ben|a2|2000
hex|a7|49
tom|a4|1250
tony|a3|150

算法知识视频讲解

Mysql

咸鱼加咸

cust_name和order_num为非聚类而在不使用group by的聚类函数中，有一个聚类sum，其他的必须也使用聚类，所以添加了这两列的group by

而在selct句子中不使用sum，即没有聚类函数，则可以在后来不用将这两列进行group by，

总结：要么全为聚类，要么全不为聚类

发表于 2022-04-10 21:58:23 回复(5)

牛客647838504号

求问，为什么还要按顾客姓名进行分组呢？

发表于 2022-03-11 20:47:54 回复(10)

Mysql

山海不可平？

select t1.cust_name,
t2.order_num,
#订单总额OrderTotal
(quantity * item_price) OrderTotal
#关联表
from Customers t1,Orders t2,OrderItems t3
#连接条件
where t1.cust_id=t2.cust_id and t2.order_num=t3.order_num
#排序
order by cust_name,OrderTotal

发表于 2022-04-24 19:58:59 回复(5)

Mysql

糖果蜗牛Q

笑死，写对了都不让通过，排行榜、讨论、题解里的赋值粘贴上去全都是错误

发表于 2022-12-08 23:07:12 回复(4)

Mysql

福行天下

select
    cs.cust_name,
    os.order_num,
    sum( oi.quantity * oi.item_price ) as OrderTotal
from Customers cs,
     Orders os,
     OrderItems oi
where cs.cust_id = os.cust_id
and os.order_num = oi.order_num

group by cs.cust_name, os.order_num

order by 1, 2

select
    cust_name,
    order_num,
    sum( quantity * item_price ) as OrderTotal
from Customers cs
    join Orders using(cust_id)
    join OrderItems using(order_num)

group by cust_name, order_num

order by 1, 2

发表于 2022-03-04 22:57:15 回复(10)

Mysql

sqlsquare

select cust_name,t3.order_num ,sum(quantity*item_price) as OrderTotal
from Customers t1 inner join Orders t2
on t1.cust_id=t2.cust_id
inner join OrderItems t3
on t2.order_num=t3.order_num
group by cust_name,t3.order_num
order by cust_name,t3.order_num

发表于 2022-03-02 18:27:04 回复(2)

Mysql

Neptune🍉🍏🍇🍊

select a.cust_name,b.order_num,convert((c.quantity*c.item_price),DECIMAL) OrderTotal from Customers a 
join Orders b  using (cust_id)
join OrderItems c  using (order_num)
order by a.cust_name,b.order_num

需要将整数变为浮点数才能通过吗？

发表于 2022-03-02 17:07:52 回复(4)

牛客623889476号

求大家帮我看看哪错了，题目要求返回的是小数，为什么我这么写返回的是整数呢，求赐教

select
  cust_name,
  order_num,
  (
    select
      sum(quantity * item_price)
    from
      OrderItems
    where
      OrderItems.order_num = Orders.order_num
  ) as OrderTotal
from
  Customers
  inner join Orders on Customers.cust_id = Orders.cust_id
order by
  cust_name,
  order_num

发表于 2022-09-23 10:03:28 回复(7)

Mysql

wydxry

select cust_name, Orders.order_num, (OrderItems.quantity*OrderItems.item_price*1.0) OrderTotal
from Customers, Orders, OrderItems
where Customers.cust_id=Orders.cust_id and Orders.order_num=OrderItems.order_num
order by cust_name, Orders.order_num

发表于 2022-03-13 23:53:33 回复(3)

Mysql

Hlyna

三种思路：

#多表联结
select
  a.cust_name,
  b.order_num,
  sum(quantity * item_price) OrderTotal
from
  Customers a
  inner join Orders b on a.cust_id = b.cust_id
  inner join OrderItems c on c.order_num = b.order_num
group by
  cust_name,
  order_num
order by
  cust_name,
  order_num;

#多条件选择，注意group by
select cust_name, b.order_num, sum(quantity * item_price) OrderTotal
from Customers a, Orders b, OrderItems c
where a.cust_id = b.cust_id and b.order_num = c.order_num
group by cust_name, order_num
order by cust_name, order_num;


#子查询
select cust_name, order_num, 
(
    select sum(quantity*item_price)
    from OrderItems
    where OrderItems.order_num = Orders.order_num

) as OrderTotal
from Customers, Orders
where Customers.cust_id = Orders.cust_id
order by cust_name, order_num

发表于 2022-10-27 17:24:40 回复(1)

Mysql

冷凡社长

select cust_name,t1.order_num,sum(quantity*item_price) as OrderTotal
from Customers t inner join Orders t1 on t.cust_id = t1.cust_id
inner join OrderItems  t2 on t1.order_num = t2.order_num
group by cust_name,t1.order_num
order by cust_name,t1.order_num

易错点：
1、因为order_num在多个表出现，所以在引用列的时候，需要注明该列来自哪个表。
2、忘记使用group by，以及忘记排序

发表于 2022-08-15 20:00:16 回复(7)

Mysql

小梁快变强

select b.cust_name,a.order_num,c.quantity*c.item_price as OrderTotal
from Orders a
join Customers b
on a.cust_id = b.cust_id
join OrderItems c
on a.order_num = c.order_num
order by b.cust_name asc,order_num asc;

发表于 2022-04-08 16:52:23 回复(4)

Mysql

JonnyOu

select cust_name,order_num,sum(quantity*item_price) as OrderTotal
from Customers
left join Orders using(cust_id)
left join OrderItems using(order_num)
group by cust_name,order_num
order by cust_name,order_num

发表于 2022-03-26 17:07:23 回复(3)

风止于秋水我止于你

select
cust_name,order_num,(quantity*item_price) as OrderTotal
from Customers
join Orders using(cust_id)
join OrderItems USING(order_num)
order by Customers.cust_name,Orders.order_num

发表于 2022-07-14 08:34:39 回复(1)

Mysql

ColaShaker

select
cust_name,
order_num,
(
select
sum(quantity * item_price)
from
OrderItems OI
where
OI.order_num = O.order_num
) OrderTotal
from
Customers C,
Orders O
where
C.cust_id = O.cust_id
order by
cust_name,
OrderTotal

发表于 2022-07-10 18:07:24 回复(1)

达达利亚~

说实话，我自己写的都有点懵，group by为什么还要加上order_num

select c.cust_name,
    o.order_num,
    round(sum(oi.quantity * oi.item_price),3) as OrderTotal
from OrderItems oi
inner join Orders o
on oi.order_num = o.order_num
inner join Customers c
on o.cust_id = c.cust_id
group by c.cust_name, o.order_num
order by c.cust_name, o.order_num

编辑于 2024-01-20 15:12:58 回复(0)

员序程马黑

select cust_name,order_num,sum(quantity*item_price) as OrderTotal
from Customers join
Orders
using(cust_id)
join
OrderItems
using(order_num)
group by cust_name,order_num
order by cust_name

发表于 2022-04-27 16:56:46 回复(0)

hunterrr

SELECT cust_name, order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers
INNER JOIN Orders USING(cust_id)
INNER JOIN OrderItems USING(order_num)
GROUP BY cust_name, order_num
ORDER BY cust_name, order_num;

发表于 2022-03-03 15:50:14 回复(0)

野蛮的熊熊求你们别卷了

select Customers.cust_name,new2.order_num,new2.OrderTotal from

(select order_num,cust_id,OrderTotal from

(select order_num,quantity*item_price as OrderTotal from OrderItems ) as new1

left join Orders

using(order_num)) new2

left join Customers using(cust_id)

order by Customers.cust_name,new2.order_num

发表于 2025-08-19 10:06:35 回复(0)

Mysql

Jeagle

SELECT a.cust_name, b.order_num,

c.quantity * c.item_price AS OrderTotal

FROM Customers AS a, Orders AS b, OrderItems AS c

WHERE a.cust_id = b. cust_id

AND b.order_num = c. order_num

ORDER BY a.cust_name, b.order_num;

为什么这道题通过率这么低。

发表于 2025-08-12 06:30:06 回复(0)

提交观点

问题信息

SQL

难度：

411条回答 3282收藏 2744浏览

通过挑战的用户

查看代码

牛客36242...

2023-03-13 00:24:37
山海不渝

2023-03-05 09:36:20
dprbt

2023-02-26 09:39:49
牛客34245...

2023-02-23 20:51:12
牛客92775...

2023-02-15 20:41:41

返回顾客名称和相关订单号以及每个订单的总价

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题