确定最佳顾客的另一种方式（一）

order_num	item_price	quantity
a1	10	105
a2	1	1100
a2	1	200
a4	2	1121
a5	5	10
a2	1	19
a7	7	5

【问题】编写 SQL 语句，根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序。

提示：总价 = item_price 乘以 quantity

【示例结果】

order_num	total_price
a1	1050
a2	1319
a4	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 ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);

输出:

a1|1050.000
a2|1319.000
a4|2242.000

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 38ms, 内存消耗: 6440KB, 提交时间: 2022-03-04

SELECT order_num, SUM(item_price * quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price >= 1000
ORDER BY order_num ASC

Mysql 解法, 执行用时: 39ms, 内存消耗: 6448KB, 提交时间: 2022-03-03

select order_num,sum(total) total_price from (
select order_num,item_price*quantity total
from OrderItems) a
group by order_num
having total_price>=1000

Mysql 解法, 执行用时: 39ms, 内存消耗: 6464KB, 提交时间: 2022-03-05

select order_num,price_sum from(
    select order_num,sum(item_price*quantity) as price_sum  from OrderItems group by order_num ) as a where price_sum>=1000 
order by order_num;

Mysql 解法, 执行用时: 39ms, 内存消耗: 6488KB, 提交时间: 2022-03-06

select 
    order_num,
    sum(item_price*quantity ) total_price
from OrderItems
group by order_num
having total_price>=1000
order by order_num;

Mysql 解法, 执行用时: 39ms, 内存消耗: 6492KB, 提交时间: 2022-03-04

select order_num,sum(item_price*quantity) as total_price
from OrderItems
group by order_num
having total_price >= 1000
order by order_num;

上一题

下一题

详情