Mysql的运行总计关闭一个表
我已经看过了几个不同的答案,这个问题似乎并不能得到查询才能正常工作。Mysql的运行总计关闭一个表
这里是我的表有列用户,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);
}
为此,可以使用标准的运行总计查询方法。但是,在这种情况下,我们也会将总和限制在特定的用户。
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不喜欢我上面给查询游戏后期,可以考虑使用单传过来的表会话变量。
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
如果这仍然给你的错误,那么你可能要考虑摆脱该临时表。无论如何,你可能不想在生产中使用临时表。
提供的链接上发生的:'警告:mysqli_fetch_array()预计参数1被mysqli_result' –
@CraigHowell我查询工作,qv演示。如果你还有其他问题,也许你在PHP代码中做了一些不正确的事情。 –
我会用PHP发布我的完整代码。在与代码交换之前它正常运行。 –
你真的有'SELECT'出现两次这样呢? –
不,这是一个小姐副本,我纠正它。 –
我已提高您的问题。我们需要一个PHP人来看待这个问题并提出意见。我认为你使用会话变量的原始方法也很好。 –