我有查询工作正常,但现在的数据是在数百万,所以当取回它说服务器没有找到。我想在这里比较当前月的前一个月的销售和前一个月的前一个月的销售,所以我的用户组按customer_id。
我看到通过查询删除组运行得更快,但我想要的输出只有客户明智,所以它必须在那里。
SELECT Customer.id,
Customer.first_name,
Customer.last_name,
SUM(Sale.`cal_qty`) AS totPurchase,
Sale2.tot2 as totPurchase2
FROM `customers` AS Customer
LEFT JOIN `sales` AS Sale
ON Sale.customer_id = Customer.id
LEFT JOIN (
SELECT s2.customer_id AS customer_id,
SUM(s2.cal_qty) as tot2
FROM sales as s2
WHERE s2.sale_date between '2015-07-23'
AND '2015-08-21'
GROUP BY s2.customer_id
) AS Sale2
ON Sale.customer_id = Sale2.customer_id
WHERE Sale.sale_date between '2015-08-22' AND '2015-09-21'
GROUP BY Sale.`customer_id`
having totPurchase > totPurchase2
ORDER BY totPurchase DESC
谁能建议我优化查询我的查询。
不确定,但你可以试试下面的查询,可能对你有帮助-
注:假设客户。Id是主键或索引,sale。Customer_id也被索引,sale。Sale_date也有索引
SELECT Customer.id,
Customer.first_name,
Customer.last_name,
sale1.totPurchase,
Sale2.tot2 AS totPurchase2
FROM `customers` AS Customer
JOIN
(
SELECT Sale.`customer_id`,
SUM(Sale.`cal_qty`) AS totPurchase
FROM `sales` AS Sale
WHERE Sale.sale_date BETWEEN '2015-08-22' AND '2015-09-21'
GROUP BY Sale.`customer_id`
) AS Sale1 ON Sale1.customer_id = Customer.id
LEFT JOIN
(
SELECT s2.customer_id AS customer_id,
SUM(s2.cal_qty) AS tot2
FROM sales AS s2
WHERE s2.sale_date BETWEEN '2015-07-23'
AND '2015-08-21'
GROUP BY s2.customer_id
) AS Sale2
ON Sale1.customer_id = Sale2.customer_id
HAVING totPurchase > totPurchase2
ORDER BY totPurchase DESC