SQL89 计算总和

描述

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