Mysql在一个表中运行总计

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

更新

更新代码由蒂姆提供，但返回错误：

$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

输出：

此处演示：

更新：

因为在游戏后期你告诉我们eks sAndSalesmantbl是一个临时表，而MySQL不喜欢我上面给出的查询，我们可以考虑使用会话变量对你的表进行一次传递。

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;

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