目标:想从订单表和客户表2张表中查询每年每个季度销售额最高的地区。
表单:客户表
订单表:
实现思路:
第一步:将月转化为季度
第二步:按照年、季度分组对比,找出季度销售额前三的地区。
代码:
SELECT sale.年,sale.季度,sale.省,sale.销售额
FROM
(SELECT YEAR(a.create_time) as 年,QUARTER(a.create_time) AS 季度,b.province AS 省,SUM(a.payment_amount) AS 销售额,COUNT(a.order_id) AS 订单数,AVG(a.payment_amount) as 平均单价
FROM order_info a INNER JOIN customer_info b
ON a.customer_id=b.customer_id
GROUP BY 年,季度,省) as sale) #对初始数据归类,形成新表
WHERE (SELECT count(*) FROM sale WHERE sale.年
=年 AND sale.季度
=季度 AND sale.销售额
<销售额)<3; #对归类后的数据按年、季度进行销售额对比,找出销售额前三的地区
问题:
报错:1146 - Table 'online_sale.sale' doesn't exist, Time: 0.014000s