php - Mysql Running Total off 一张表

Question

我已经查找了这个问题的几个不同答案，但似乎无法使查询正常工作。

这是我的表，其中包含列 user、weekNo、salesTotalYTD。

我目前正在将它们取出并按周对它们进行分组，如下所示:

+------+--------+---------------+
| user | weekNo | salesTotalYTD |
+------+--------+---------------+
|Jared |   1    |      200      |
+------+--------+---------------+
|  Jim |   1    |      50       |
+------+--------+---------------+
|Jared |   2    |      30       |
+------+--------+---------------+
| Jim  |   2    |      100      |
+------+--------+---------------+

我想做但无法完成的是以下内容:

+------+--------+---------------+
| user | weekNo | salesTotalYTD |
+------+--------+---------------+
|Jared |   1    |      200      |
+------+--------+---------------+
| Jim  |   1    |      50       |
+------+--------+---------------+
|Jared |   2    |      230      |
+------+--------+---------------+
| Jim  |   2    |      150      |
+------+--------+---------------+

这是我在第一次通过时使用的查询，但之后的每次通过都是错误的:

SET @runtot:=0

SELECT
    salesTotalYTD,
    user,
    (@runtot := @runtot + salesTotalYTD) AS rt
  FROM weeksAndSalesmantbl
  GROUP BY user, weekNo
  ORDER BY (CASE WHEN weekNo = 52 THEN 0 ELSE 1 END) ASC, weekNo, user ASC

已更新

更新代码由 Tim 提供但返回错误:

$assignments = "
    SELECT
    t1.user,
    t1.weekNo,
    (SELECT SUM(t2.salesTotalYTD) FROM weeksAndSalesmantbl t2
    WHERE t2.user = t1.user AND t2.weekNo <= t1.weekNo) AS salesTotalYTD
    FROM weeksAndSalesmantbl t1
    ORDER BY
    t1.weekNo,
    t1.user";

  $salesTotalSalesManCumulative = [];

  $assignmentsqry = mysqli_query($db,$assignments);

  if (!$assignmentsqry) {
     printf("Error: %s\n", mysqli_error($db));
     exit();
  }

  while ($row = mysqli_fetch_array($assignmentsqry)) {

    $float = floatval($row['salesTotalYTD']);
    $float = round($float,2);

    array_push($salesTotalSalesManCumulative,$float);

  }

Answer 1

您可以使用标准运行总计查询来解决这个问题。但是，在这种情况下，我们还将总和限制为特定用户。

SELECT
    t1.user,
    t1.weekNo,
    (SELECT SUM(t2.salesTotalYTD) FROM weeksAndSalesmantbl t2
     WHERE t2.user = t1.user AND t2.weekNo <= t1.weekNo) AS salesTotalYTD
FROM weeksAndSalesmantbl t1
ORDER BY
    t1.weekNo,
    t1.user

输出:

此处演示:

Rextester

更新:

由于在游戏后期您告诉我们 weeksAndSalesmantbl 是一个临时表，而 MySQL 不喜欢我在上面给出的查询，我们可以考虑使用 session 变量对您的表进行单次传递。

SET @rt = NULL;
SET @user = NULL;

SELECT
    t.user,
    t.weekNo,
    t.rt AS salesTotalYTD
FROM
(
    SELECT
        @rt:=CASE WHEN @user=user THEN @rt+salesTotalYTD ELSE salesTotalYTD END AS rt,
        @user:=user AS user,
        weekNo
    FROM weeksAndSalesmantbl
    ORDER BY
        user,
        weekNo
) t
ORDER BY
    t.weekNo,
    t.user;

Demo

如果这仍然给您带来错误，那么您可能需要考虑摆脱那个临时表。无论如何，您可能不希望在生产中使用临时表。