MySQL根据相同的值计算

Question

我有这个查询:

SELECT `item_code`, 
       `q_rr`, 
       `q_srs`, 
       @running_bal := @running_bal + (`q_rr` - `q_srs`)  as `Balance`
FROM   records, (SELECT @running_bal := 0) tempName
order by
      records.item_code,
      records.date

结果是:

 item_code  |  q_rr  | q_srs |    balance
--------------------------------------------
 0F02206A       2        0           2
 BR00113D       3        0           5
 BR00114D       10       0           15
 BR00114D       0        1           14
 BR00114D       0        1           13
 BR00115D       20       0           33
 BR00115D       0        1           32
 BR00115D       0        1           31

需要帮助来计算余额，如果 q_rr(+) 和 q_srs(-) 并按 item_code 进行计算。

 item_code  |  q_rr  | q_srs |    balance
--------------------------------------------
 0F02206A       2        0           2
 BR00113D       3        0           3
 BR00114D       10       0           10
 BR00114D       0        1           9
 BR00114D       0        1           8
 BR00115D       20       0           20
 BR00115D       0        1           19
 BR00115D       0        1           18

Answer 1

我添加了@code变量来跟踪item_code更改:

SELECT
  r.item_code,
  r.q_rr, 
  r.q_srs,
  @running_bal := IF(@code = r.item_code, @running_bal, 0) + (r.q_rr - r.q_srs) as Balance,
  @code := r.item_code AS dummy
FROM
  records r
CROSS JOIN
  (SELECT @running_bal := 0, @code := '') tempName
ORDER BY
  r.item_code,
  r.date

SQL fiddle :http://sqlfiddle.com/#!2/04ef2b/11

您可以通过将其作为子查询放入另一个选择中来消除虚拟列:

SELECT item_code, q_rr, q_srs, Balance
FROM (
  -- there put first query
) r

SQL fiddle :http://sqlfiddle.com/#!2/04ef2b/12