这是完整的查询,每行没有子选择:(感谢@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 := 0) ct
ORDER BY 1;
原表:
date | totalsales
=======================
2014-05-01 | 3000
2014-05-02 | 3100
2014-05-03 | 3500
2014-05-04 | 3650
OUTPUT
date | sales | average | growth
=======================================
2014-05-01 | 0 | 0 | 0
2014-05-02 | 100 | 50 | 100
2014-05-03 | 400 | 166.66 | 400
2014-05-04 | 150 | 162.5 | 37.5