国外设计网站上海网上推广
描述
OrderItems表代表订单信息，包括字段：订单号order_num和item_price商品售出价格、quantity商品数量。
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
答案
select * from (
select order_num,sum(item_price*quantity) as total_price
from OrderItems t1
group by t1.order_num) t2
where t2.total_price>=1000
order by t2.order_num①先使用sum()函数计算出所有订单的总价
sql：
select order_num,sum(item_price*quantity) as total_price
from OrderItems t1
group by t1.order_num计算后结果：
order_num | total_price |
a1 | 1050 |
a2 | 1319 |
a4 | 2242 |
a5 | 50 |
a7 | 35 |
②排除总价小于1000 的，并根据订单号order_num排序
sql：
select * from (第一步结果sql) t2
where t2.total_price>=1000
order by t2.order_num计算后结果：
order_num | total_price |
a1 | 1050 |
a2 | 1319 |
a4 | 2242 |