mysql - 运行总 mysql 查询 - 几乎可以工作

Question

我即将完成一个运行总计专栏，但我认为我遗漏了一些简单的东西。任何帮助将不胜感激。

SET @runtot := 0;
SELECT
  COUNT(adjustment_id) AS Adjustments,
  DATE(FROM_UNIXTIME(shifts.outtime)) AS 'Month',
  (@runtot := @runtot + COUNT(adjustment_id)) AS RT
FROM adjustments
  INNER JOIN shifts
    ON (shifts.shiftID = adjustments.shiftID)
  INNER JOIN employees
    ON (shifts.idnum = employees.idnum)
WHERE YEAR(FROM_UNIXTIME(shifts.outtime)) = '2012'
GROUP BY MONTH(FROM_UNIXTIME(shifts.outtime))
ORDER BY MONTH(FROM_UNIXTIME(shifts.outtime))ASC

上面的代码输出:

Adjustments | Month      | RT
34          | 2012-08-29 | 34
161         | 2012-09-01 | 161

RT 与调整相匹配，并没有像我希望的那样显示运行总数。

预先感谢您的帮助!

Answer 1

您需要对聚合查询的结果进行累计:

SET @runtot := 0;

SELECT 
    Adjustments,
    Month,
    (@runtot := @runtot + Adjustments) AS RT
FROM ( SELECT
COUNT(adjustment_id) AS Adjustments,
DATE(FROM_UNIXTIME(shifts.outtime)) AS 'Month'
FROM
adjustments
INNER JOIN shifts ON (
shifts.shiftID = adjustments.shiftID
)
INNER JOIN employees ON (shifts.idnum = employees.idnum)
WHERE
YEAR (FROM_UNIXTIME(shifts.outtime)) = '2012'
GROUP BY MONTH(FROM_UNIXTIME(shifts.outtime))
ORDER BY MONTH(FROM_UNIXTIME(shifts.outtime)) ASC
) x