error on over()子句根据id计算mysql中下一个或上一个值的行

e5nszbig 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(384)

我面临着一个问题，我的查询没有执行，我得到了over子句附近的错误，但我希望得到如下图片所示的结果：

我的意思是我想要25000-9000=16000这样的结果；16000-5000 =11000; 像这样显示输出，这是我的查询

query:
SELECT s.id, s.cust_id, 
s.package_name,s.pending_amount,s.pack_received_amount, s.return_amount, 
s.payment_type,s.total_package_amount, SUM('s.pending_amount') OVER (ORDER 
BY s.id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS s.balance FROM 
payment s WHERE s.cust_id = S1307 and s.package_name= switzerland

我的错误是这里的图片截图：

sql mysql php codeigniter-2

来源：https://stackoverflow.com/questions/53774810/error-on-over-clause-for-am-calculate-the-rows-based-on-ids-to-next-or-previo

2条答案

按热度按时间

vltsax251#

SELECT Total,
Received,
CASE WHEN @balance = 0 THEN
(@balance := Total - Received) 
ELSE 
@balance := @balance - Received
END AS Balance
FROM
Table1,
(select @balance:=0) as t2

表1

Total   Received
-----------------
25000   9000
25000   5000

输出

Total   Received    Balance
----------------------------
25000   9000        16000
25000   5000        11000

演示
http://sqlfiddle.com/#!2018年9月43日

赞(0）回复(0）举报 2021-06-18

nqwrtyyt2#

这是您的问题：

select . . .,
       sum(s.pending_amount) over (order by s.id) as balance 
from payment s 
where s.cust_id = S1307 and s.package_name = 'switzerland';

如果没有太多行，一个简单的子查询就足够了：

select . . .,
       (select sum(s2.pending_amount)
        from payment s2
        where s2.id <= s.id and
              s2.cust_id = s.cust_id and
              s2.package_name = s.package_name
       ) as balance 
from payment s 
where s.cust_id = 'S1307' and
      s.package_name = 'switzerland';

赞(0）回复(0）举报 2021-06-18

我来回答

error on over()子句根据id计算mysql中下一个或上一个值的行

2条答案

相关问题

热门标签

最新问答