php - 在mysql查询中创建和更新虚拟列值

我的表结构是

customer  column1 column2(int)
  1        sale      2
  1        return    1
  1        sale      3
  1        sale      1
  1        return    1

题：-


 创建一个虚拟列（column3），其起始值为“ column3 = 0”，并根据“ column1”和“ column2”的值更新其值。我找不到同时创建和更新虚拟列的查询。


例如IF(column1='sale',column3 + column2, column3-column2 )

// start value of column3 = 0 for each product
customer  column1 column2(int) column3
  1        sale      2             2  //0+2 because it is a sale
  1        return    1             1  //2-1 because it is a return
  1        sale      3             4  //1+3 because it is a sale
  1        sale      1             6  //4+2 because it is a sale
  1        return     1            4  //6-2 because it is a return

最佳答案

CROSS JOIN应该解决这个问题：

SELECT
    t.column1,
    t.column2,
    CASE
WHEN t.column1 = 'sale' THEN
    @id :=@id + t.column2
ELSE
    @id :=@id - t.column2
END AS column3
FROM
(SELECT @id:=0) var
CROSS JOIN table1 t

关于php - 在mysql查询中创建和更新虚拟列值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35602662/