mysql - 有没有办法在 mysql View 中计算余额？

Question

我想从mysql计算余额

200  
270 
295 
285 
270 
250 

我努力了但没有成功

SELECT `id`, `in`, `out`,balance
FROM (SELECT t.*,
         @n := IF(@g <> id, 0, @n) + COALESCE(in,0) - COALESCE(out, 0) balance,
         @g := id 
      FROM product_trans t,
          (SELECT @n := 0) n,
          (SELECT @g := 0) g ORDER BY id ) q

Answer 1

SELECT t1.id, SUM(t2.`in` - t2.`out`) AS balance
FROM tab t1
JOIN tab t2
  ON t2.id <= t1.id
GROUP BY t1.id
ORDER BY t1.id;

SqlFiddleDemo

输出:

╔═════╦═════════╗
║ id  ║ balance ║
╠═════╬═════════╣
║  1  ║     200 ║
║  2  ║     270 ║
║  3  ║     295 ║
║  4  ║     285 ║
║  5  ║     270 ║
║  6  ║     250 ║
╚═════╩═════════╝