mysql - 想要获得佣金 SQL 表的运行总计

Question

我试图从一张表“policies”中获得以下结果

+-------------+------------------+
|Commission   |Total Commission  |
+-------------+------------------+
|108          |108               |
+-------------+------------------+
|50           |158               |
+-------------+------------------+
|12           |170               |
+-------------+------------------+

and so on...

到目前为止，我的查询如下所示:

SELECT
  P.commission
  SUM(P.commission) OVER (PARTITION BY P.id ORDER BY P.id) AS "Total Commission"
FROM
  policies P

除了使用 OVER 似乎不起作用(例如 SELECT、SUM() 和 FROM 全部将文本颜色更改为蓝色，因为它们是关键字)

请有人建议一种方法让它按照我想要的方式工作

EDIT:: p.policy_id didn't exist. Was just p.id, corrected.
EDIT2:: incorrect spelling of "commission", corrected

进一步编辑:

我现在使用以下查询，该查询正在获取以下结果:

SELECT p.commission,
   (SELECT SUM(p2.commission)
    FROM policies p2
    WHERE p2.id = p.id AND p2.id <= p.id
   ) AS total_commission
FROM policies p;


+-------------+------------------+
|Commission   |Total Commission  |
+-------------+------------------+
|108          |108               |
+-------------+------------------+
|50           |50                |
+-------------+------------------+
|12           |12                |
+-------------+------------------+

Answer 1

您可能使用的是 MySQL，它不支持窗口函数。

您可以使用相关子查询来代替:

SELECT p.comission,
       (SELECT SUM(p2.comission)
        FROM policies p2
        WHERE p2.id = p.id and p2.policy_id <= p.policy_id
       ) as total_comission
FROM policies p;