淘宝联盟网站建设源码,护肤品推广软文,怎样开始学做自媒体,菏泽市建设职工培训中心网站描述OrderItems表代表订单信息#xff0c;包括字段#xff1a;订单号order_num和item_price商品售出价格、quantity商品数量。order_numitem_pricequantitya110105a211100a21200a421121a5510a2119a775【问题】编写 SQL 语句#xff0c;根据订单号聚合#xff0c;返回订单总…描述OrderItems表代表订单信息包括字段订单号order_num和item_price商品售出价格、quantity商品数量。order_numitem_pricequantitya110105a211100a21200a421121a5510a2119a775【问题】编写 SQL 语句根据订单号聚合返回订单总价不小于1000 的所有订单号最后的结果按订单号进行升序排序。提示总价 item_price 乘以 quantity【示例结果】order_numtotal_pricea11050a21319a42242示例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.000a2|1319.000a4|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_price1000
order by t2.order_num①先使用sum()函数计算出所有订单的总价sqlselect order_num,sum(item_price*quantity) as total_price
from OrderItems t1
group by t1.order_num计算后结果order_numtotal_pricea11050a21319a42242a550a735②排除总价小于1000 的并根据订单号order_num排序sqlselect * from (第一步结果sql) t2
where t2.total_price1000
order by t2.order_num计算后结果order_numtotal_pricea11050a21319a42242
