这是完整的查询,每行没有子选择:(感谢@nmarsh编写最难的部分)
SELECT
t1.Date,
CASE
WHEN t2.date IS NULL THEN 0 ELSE (t1.totalSales - t2.totalSales)
END AS sales,
CASE
WHEN t2.date IS NULL THEN 0 / (@curRow := @curRow + 1) ELSE ((@curSum := @curSum + (t1.totalSales - t2.totalSales)) / (@curRow := @curRow + 1))
END AS average,
CASE
WHEN t3.date IS NULL AND t2.date IS NULL THEN 0
WHEN t3.date IS NULL THEN (t1.totalSales - t2.totalSales)
WHEN t2.date IS NULL THEN 0 ELSE ((t1.totalSales - t2.totalSales) * 100) / (t2.totalSales - t3.totalSales)
END AS growth
FROM test t1
LEFT JOIN test t2 ON t2.date = DATE_ADD(t1.Date, INTERVAL -1 DAY)
LEFT JOIN test t3 ON t3.date = DATE_ADD(t2.Date, INTERVAL -1 DAY)
JOIN (SELECT @curRow := 0) r
JOIN (SELECT @curSum :