php中的自定义累积和

zvokhttg  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(324)

我在php-mysql中成功地使用了累计和。

+---+---------+
| id| SomeNumt|
+---+---------+
|  1|       10|
|  2|       12|
|  3|        3|
|  4|       15|
|  5|       23|
+---+---------+

SELECT t1.id,
       t1.SomeNumt,
       SUM(t2.SomeNumt) AS SUM
FROM t t1
INNER JOIN t t2 ON t1.id >= t2.id
GROUP BY t1.id,
         t1.SomeNumt
ORDER BY t1.id

输出:

+---+---------+--------+
| id| SomeNumt|     SUM|
+---+---------+--------+
|  1|       10|      10|
|  2|       12|      22|
|  3|        3|      25|
|  4|       15|      40|
|  5|       23|      63|
+---+---------+--------+

现在我有一个小问题,当我自定义查询( WHERE t1.SomeNumt>3 )

...
INNER JOIN t t2 ON t1.id >= t2.id
WHERE t1.SomeNumt>3
...

输出错误

+---+---------+--------+
| id| SomeNumt|     SUM|
+---+---------+--------+
|  1|       10|      10|
|  2|       12|      22|
|  4|       15|      40|
|  5|       23|      63|
+---+---------+--------+

但我期待着:

+---+---------+--------+
| id| SomeNumt|     SUM|
+---+---------+--------+
|  1|       10|      10|
|  2|       12|      22|
|  4|       15|      37|   -- 10+22+15
|  5|       23|      60|   -- 10+22+15+23
+---+---------+--------+

如何修复?

vc6uscn9

vc6uscn91#

你需要把同样的条件应用到 t2 :

SELECT t1.id,
       t1.SomeNumt,
       SUM(t2.SomeNumt) AS SUM
FROM t t1
INNER JOIN t t2 ON t1.id >= t2.id
WHERE t1.SomeNumt > 3 AND t2.SomeNumt > 3
GROUP BY t1.id,
         t1.SomeNumt
ORDER BY t1.id

或者,可以在子查询中应用条件:

SELECT t1.id,
       t1.SomeNumt,
       SUM(t2.SomeNumt) AS SUM
FROM (SELECT * FROM t WHERE SomeNumt > 3) t1
INNER JOIN (SELECT * FROM t WHERE SomeNumt > 3) t2 ON t1.id >= t2.id
GROUP BY t1.id,
         t1.SomeNumt
ORDER BY t1.id

输出(对于两个查询):

id  SomeNumt    SUM
1   10          10
2   12          22
4   15          37
5   23          60

在dbfiddle上演示
如果您使用的是mysql 8+,那么应该使用@gordonlinoff的答案中描述的窗口函数。

blpfk2vs

blpfk2vs2#

如果要累积和,请使用窗口函数:

SELECT t.id, t.SomeNumt,
       SUM(t2.SomeNumt) OVER (ORDER BY t.id) AS SUM
FROM t
ORDER BY t.id;

如果你加一个 WHERE 条款,这应该是你想要的。

相关问题