mysql计算表中的销售总额_php – 计算MySQL中的每日销售额

这是完整的查询,每行没有子选择:(感谢@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